The Definitive Guide to DAX_ Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel, Second Edition by Marco Russo & Alberto Ferrari

Author:Marco Russo & Alberto Ferrari [Marco Russo & Alberto Ferrari]
Language: eng
Format: epub
Publisher: Microsoft Press
Published: 2000-01-02T00:00:00+00:00

The result of EXCEPT filters the Customer[CountryRegion] column because it is the column used by the table taken as the first argument of EXCEPT.

Using tables as filters

Functions manipulating tables are oftentimes used to build complex filters for CALCULATE parameters. In this section, we provide further examples, always leading you one step further in your understanding of DAX.

Implementing OR conditions

A first example where manipulating tables proves to be a useful skill is the following. Imagine having to implement an OR condition between the selections made in different slicers, instead of the default AND behavior provided by client tools like Excel and Power BI.

The report in Figure 12-14 contains two slicers. The default behavior of Power BI is to intersect the two conditions. As a consequence, the numbers shown represent the sales of Home Appliances to customers with a High School education.

Figure 12-14 By default, slicer conditions are intersected so that all the conditions are applied together. Instead of intersecting the two conditions, one might want to merge them. In other words, the numbers shown in the report need to be the sales of products sold to customers with a High School education or the sales of Home Appliances. Because Power BI does not support “or” conditions between slicers, one can solve the problem by using DAX.

Remember that each cell of the report has a filter context containing both a filter on the category and a filter on the education. Both filters need to be replaced. There are several possible solutions to the same pattern; we demonstrate the use of three different formulas.

The first, and probably the easiest expression of that filter, is the following:

Click here to view code image

OR 1 := VAR CategoriesEducations = CROSSJOIN ( ALL ( 'Product'[Category] ), ALL ( Customer[Education] ) ) VAR CategoriesEducationsSelected = FILTER ( CategoriesEducations, OR ( 'Product'[Category] IN VALUES ( 'Product'[Category] ), Customer[Education] IN VALUES ( Customer[Education] ) ) ) VAR Result = CALCULATE ( [Sales Amount], CategoriesEducationsSelected ) RETURN Result

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.