Problem:
Suppose you have a table of transactions and a product class dimension. You want to be able to dynamically set a different discount amount for each product class (Deluxe, Economy, and Regular).
So you create a parameter for each product class:
And you create a measure called 'Adjusted Sales' where you multiply the sales amount by the corresponding product class discount.
The Challenge
The challenge, however, is that you need to reference each selected parameter in the measure, taking into account the product class in the filter context. You could start out writing the following:
Adjusted Sales =
VAR _ProductClass =
SELECTEDVALUE( Products[Product Class] )
RETURN
SWITCH ( _ProductClass,
"Deluxe", [Total Sales] * 'Class Name - Delux'[Class Name - Delux Value],
"Economy", [Total Sales] * 'Class Name - Economy'[Class Name - Economy Value],
"Regular", [Total Sales] * 'Class Name - Regular'[Class Name - Regular Value],
[Total Sales]
)This works... but only when a single product class is available in the filter context. Indeed, the above code breaks when no product class is visible, like in the row total:
We can use a SUMX iterator to correct this problem. By iterating over the Product Classes, we can guarantee a product class is used for the calculation, even for the row total:
Adjusted Sales 2 =
SUMX(
VALUES ( Products[Product Class] ),
SWITCH ( Products[Product Class],
"Deluxe", [Total Sales] * 'Class Name - Delux'[Class Name - Delux Value],
"Economy", [Total Sales] * 'Class Name - Economy'[Class Name - Economy Value],
"Regular", [Total Sales] * 'Class Name - Regular'[Class Name - Regular Value],
[Total Sales]
)
)
This now works. But can we do better? If you have many more parameters, (like states, for example you may have 50), the code above will make your report crawl to a halt (potentially). Why? Let's say you are iterating over 50 states. For each state, you are invoking two costly context transitions. One for the [Total Sales] and the other for the parameter value. Consider a model with 2M sales transactions, and for each state parameter you have 20 values to pick from. Let's do the math.
2M transactions x 50 states x 20 parameter options = 2B iterations.
Optimizing Multiple Parameters for a DAX Measure
Here's the idea... If we can "convert" each of the parameters into a table that contains the product class data lineage, we can remove some unnecessary context transition.
If we can introduce some lineage to our parameters, we can leverage more of the storage engine to help us improve the speed of the calculations. Some of the functions that will help us accomplish this include TREATAS and UNION.
Adjusted Sales 3 =
VAR _UnionedParameters =
FILTER(
UNION(
SELECTCOLUMNS(
'VALUES( Class Name - Deluxe'[Class Name - Deluxe]),
"@Class", "Deluxe",
"@SelectedValue", [Class Name - Deluxe]),
SELECTCOLUMNS(
'VALUES( 'Class Name - Economy'[Class Name - Economy]),
"@Class", "Economy",
"@SelectedValue", [Class Name - Economy]),
SELECTCOLUMNS(
'VALUES( 'Class Name - Regular'[Class Name - Regular]),
"@Class", "Regular",
"@SelectedValue", [Class Name - Regular])
),
[@Class] IN VALUES ( Products[Product Class] )
)
RETURN
SUMX(
_UnionedParameters,
CALCULATE(
[Total Sales],
TREATAS( { [@Class] }, Products[Product Class] )
) * [@SelectedValue]
)Explaination
_UnionedParameters
The first variable combines values from each of our class parameter tables. It then filters the resulting combined table based on the outer filter context. If we didn't have this filter, then each row in our visual (table) will show the grand total, unfiltered for the product class.
You may be asking yourself... "Why are you adding a duplicated column for the value?" Well, we need this for two reasons. By adding the duplicate column and calling it "@SelectedValue", we 1) insure the columns line up correctly for the UNION and 2) make it easier to understand the code in the SUMX (we are multiplying by the "@SelectedValue").
Returning the SUMX
The SUMX iterates over the table computed in the previous step (_UnionedParameters) and performs our needed calculation. It multiplies each parameter value ("@SelectedValue") by the corresponding 'Total Sales' for that class. The key here is the TREATAS function, which allows us to use the column we defined, "@Class" to be used as a filter for the Product Class table. SUMX then sums up each of these adjusted sales values to give us the final result.
Performance Differences
Okay now on to the juicy stuff! Good old performance tests. Earlier I had stated that the first version of our code was slow due to two reasons. 1) Many context transitions and 2) unnecessary iterations.
Before getting to the results, note that my model is not very big, so even though the difference here is negligible, improvements on a larger model with more complex calculations can be significant.
Un-Optimized Measure
The main thing to notice here is that there are 3 lines (6, 8 and 10) that perform the calculation (corresponding to the three parameters we have).
Optimized Measure
Though this measure has the same amount of queries as the un-optimized one, it is much better. In fact, there is only one query (number 10) that does the entire calculation for us (compared to three queries needed for the un-optimized version). The other queries are only for gathering rows required for the aggregation (three parameter tables, plus the Product Class table. Indeed, we see this measure ran in 17 ms compared with 23 ms for the un-optimized measure.
Conclusion
While optimizing is fun, it often results in less-than-readable code. Indeed, the optimized code I wrote for the article is much more intimidating than the un-optimized version. Therefore, I would only recommend you take this route if your report is truly suffering from horrendous performance. In such a case, using UNION to append your parameter tables together, combined with a TREATAS, can provide huge performance gains. And remember, the reason it's faster is because we avoided unnecessary context transitions and shrank the number of iterations.
