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 […]
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 […]
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 […]
