Using the new InScope function for DAX that was introduced in November 2018 release of Power BI desktop, we can now customize the calculation in measures according to the hierarchy level where they are currently being used.
To illustrate the same, we have a Power BI Report where we would like to compare the price of a product across various online retailers. We use the dimensions in a matrix, displaying them in a hierarchy. Now, using the regular measure we will not be able to customize how the measure behaves at each level of the hierarchy. For example, showing the aggregated value of unit price across the Product Name does not make any sense. Neither does showing the aggregated value of unit price across the Product Category.
Using the InScope function, we can create a new measure that is sensitive to the hierarchy level.
We specify that if the dimension for individual retailers is in scope i.e., measure is at the lowest hierarchy level, the usual values can be displayed. If the dimension for Product Name is in scope, we can specify that the measure shows the lowest price available for the product across all retailers.
The new measure works as below.
At the retailer level, it displays the price of the product on the website. At the Product Level, it displays the lowest price available for the product across all retailers. At the Product Category Level, no value is displayed.
Visual BI also offers a set of Powerful Advanced Visuals for Power BI – Check the visuals here. Learn about the hierarchy tree for Power BI here. Learn more about Microsoft Power BI services offerings from Visual BI solutions here.