Okay – so something (actually a few things) have come up at work recently and this caused me to miss my planned SSRS parameterized DAX post. I’ll try to get to this soon as it’s a powerful and practical way of exposing an SSAS solution and DAX queries to users.
As a short term substitute for the more lengthy SSRS post, I have a real life story from work.
The situation:
The business wanted a single metric which sums a column (‘Units’) from the Sales Table when the value of a certain dimension’s attribute was ‘TW’. However, if the dimension’s attribute wasn’t ‘TW’, the metric needed to sum the units from a different table’s column.
I wrote a metric (‘TotalUnits’) that applies this contextual logic at execution time and added it to the Excel report:
The Code:
=IF(HASONEVALUE(Method[Fulfillment Method]),
SWITCH(TRUE(),VALUES(Method[Fulfillment Method])=”TW”,SUM(Sales[Units]),
SUM(OMS[Units])),SUM(Sales[Units])+SUM(OMS[Units]))
Takeaways:
There are multiple ways of solving this issue but this is a common pattern where the VALUES() function and its related functions such as HASONEVALUE come into play in order to read the filter context on the fly. HASONEVALUE is essential for subtotal and grand total cells – without a condition that tests for multiple values in the filter the grand total or subtotal cells would be blank as the metric wouldn’t have a rule to follow.
So here’s a step by step explanation of the metric:
1. Test for a single value (e.g. TW) of the column
2. If HASONEVALUE evaluates to TRUE, apply the SWITCH function.
2a. The SWITCH function simply evaluates pairs of expressions and their corresponding values, returning the value associated with the first expression that is TRUE. In this case we test for TW first and if this fails we sum the other column since we know there’s only one other value in the filter context per Step 1.
3. If HASONEVALUE evaluates to FALSE, simply sum both columns from both fact tables. This is what happens in the ‘415’ cell in the image above. (It’s natural to think that the pivot table just sums the cells to arrive at 415 but it doesn’t work that way – the Total value has its own filter context calculation. This point is important in many scenarios in which the totals don’t necessarily reflect the sum of the parts (unless we force them to). I may have a blog post on this sometime.
Sorry about the delay in new content. I’ll try to get to the SSRS parameterized DAX report soon.
-Brett
Insight Quest 