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: