Use CALCULATE to manipulate filters Earlier in this chapter, you saw that the CALCULATE function can be used to alter relationships when paired with other DAX measures. The USERELATIONSHIP function with CALCULATE can activate inactive relationships, and CROSSFILTER with CALCULATE can change the filter direction. The CALCULATE function also allows you to alter the filter […]
Removing filters There are several DAX functions that you can use as CALCULATE modifiers to ignore filters, one of which is ALL. ALL can remove filters from: Important Sort By Column and ALL If you’re removing filters from a column that is sorted by another column, you should remove filters from both columns—otherwise, you may […]
Context transition Another important function of CALCULATE is context transition, which refers to transitioning from row context to filter context. In DAX, there are two evaluation contexts: To review context transition, let’s create a sample table in the data model: On the Home ribbon, select Enter data. Enter Sample in the Name box. Enter the […]
Implement Time Intelligence using DAX It is common for business users to want to aggregate metrics—for example, revenue—across time, such as year-to-date revenue for a certain date, or prior-year revenue for the comparable period. Fortunately, DAX has a family of functions, referred to as Time Intelligence, that facilitate such calculations. All Time Intelligence functions require […]
Replace implicit measures with explicit measures It is sometimes possible to replace some numeric columns with measures, which can reduce the size of the data model. In our Wide World Importers example, there are several columns that could be replaced with measures. For example, the Total Chiller Items and Total Dry Items columns in the […]
Create semi-additive measures In general, there are three kinds of measures: In this section, we focus on semi-additive measures. There are several ways to write a semi-additive measure, and the correct way for you depends on your business requirements. Let’s say your business is interested in inventory counts, and you have the data model shown […]
Use quick measures A measure in Power BI is a dynamic evaluation of a DAX query that will change in response to interactions with other visuals, enabling quick, meaningful exploration of your data. Creating efficient measures will be one of the smartest things you can do to build insightful reports. If you’re new to DAX […]
Skill 2.4: Optimize model performance Sometimes after you create the first version of your data model, you may realize that it doesn’t perform well enough. Because of the way Power BI stores data, it may mean that your data model isn’t performing as efficiently as it can. In this section, we review the skills necessary […]
Remove unnecessary columns Columns in a data model usually serve at least one of two purposes: they could be used to support visuals or calculations, or both. It’s preferable to not load columns that aren’t used for any purpose, especially if they’ve got a high number of distinct values. Some data warehouses include primary keys […]
Reduce cardinality levels to improve performance Power BI employs several compression mechanisms to reduce the size of data, the details of which are outside the scope of this book. One way to decrease the data size, which we cover next, is by reducing the cardinality of columns by changing data types or the default summarization. […]
