I got a question that proved more challenging than I imagined.
I have a report that displays the following values. It shows Days in stock per employee per Continent and per Country. The days in stock is a calculated measure:
=DimGeography[DaysInStock per store] / DimGeography[NrOfEmployees in service]
which again contains of two calculated measures. The question i got was if it was possible to show the minimal value of the measure [days in stock per employee] for each country grouped by Continent:
In this case the minimal value of the country’s of the continent Europe is 2.483,69. So how can we achieve this? The biggest problem is that we are unable to use the MIN function, the function MIN only takes a column, not a measure. So again we resort to the The 5-point palm, exploding fxn technique in this case MINX.
I want to start by getting the measure for each DimGeography[RegionCountryName] into a Min function. This looks like:
=MINX(values(DimGeography[RegionCountryName]),DimGeography[Days In stock per employee])
Only this returns the minimal value of [Days In stock per employee] for each [RegionCountryName], this is the same result as the regular measure. MINX uses the same context as all other DAX functions.
The reason we use values(DimGeography[RegionCountryName]) in stead of the table DimGeography is because of the performance. MINX steps through each rows of the passed trough parameter, as you can imagine stepping through a single column is much better performance wise than an entire table. By using Values we get only one column from a table.
Ok MINX uses the context as other DAX functions, that means we can change it using our almight CALCULATE function as well, this gives us:
=CALCULATE(
MINX(values(DimGeography[RegionCountryName]),
DimGeography[Days In stock per employee]),
ALL(DimGeography[RegionCountryName]))What happens here is that we change the context to include all RegionCountryName’s within the group continent.
The measure gives this result in the PowerPivottable:
Hi Lee,Great explanation of the inner/outer ctxnetos I particularly love the visualizations with the 2 circles! However, I believe the explanation is a bit misleading. It’s not the SUMX which provides the outer context. In fact, SUMX doesn’t know which table it operates on (and thus which is the current context) before the full completion of FILTER. What provides the outer context is PowerPivot which is looping through all rows of the table in order to evaluate the calculated column. You can check this by doing an EVALUATE of the formula in DAX Studio. Without having a calculated field, it will fail with “EARLIER/EARLIEST refer to an earlier row context which doesn’t exist.” which will show you that the SUMX by itself is not creating an outer context.Keep up the good work!
Great work! I was wondering if there is a way using the same data to return only the continents with their calculated min value and it’s related country… The problem related to the min value is solved as showed in this post… But how to show in a specific column which country is related to that min value?