Scott Senkeresty on 25 Jun 2016 23:19:16
I am calling them global variables, because "Measures that can return tables" ... probably confuses too many issues.
Say I have a custom calendar with exactly 52 weeks per year.
Prior Year := FILTER(ALL(Calendar), Calendar[WeekNum] = MAX(Calendar[WeekNum] - 52))
^^ Not valid today.
If it was, I could then do...
Sales - PY := CALCULATE([Total Sales], [Prior Year])
Profit - PY := CALCULATE([Total Profit], [Prior Year])
Maybe:
VAR Prior Year := FILTER(ALL(Calendar), Calendar[WeekNum] = MAX(Calendar[WeekNum] - 52))
And call it a "global variable" or Table Measure or ... something?
- Comments (13)
RE: Add global variables to DAX
Something like this is definitely needed for larger and more complex models. Specifically the first use case as Daniel states below.
The idea of a global variable is not tied to table variables only, although scalar variables can be implemented through measures today. The fundamental difference with a calculated table, obviously, is that a global variable is evaluated at reporting time in a context-dependent way.
RE: Add global variables to DAX
I am looking forward for this feature as well.
RE: Add global variables to DAX
'This idea is extremely under-appreciated. To help others searching for the same feature, let me add some additional keywords that hopefully clarifies the point of this (so the idea can get the votes it deserves):
- Dynamic table
- Shared table variable
- Table-valued measure
- Reuse table variable
- Global filter context
- Shared filter context
- Reuse filter context
Let me try to explain why this feature would be immensely powerful and useful to all sorts of Tabular modeling:
1. Encapsulation of filtering logic into reusable pieces:
Within the context of a single measure or calculated column, DAX variables are incredibly useful for logic that needs to be reused several places. It makes the code more readable, and my experience is that Formula Engine performance also improves through the proper use of variables. For logic that depends on a single value, today we can easily use intermediate measures to achieve encapsulation, and reuse logic across several measures. However, we don't have a way of doing the same thing for logic that depends on a filter context, which means that you will often need to do the exact same table calculation or filtering in multiple measures and calculated columns, making the solution more cumbersome and difficult to maintain. If the logic changes slightly, the change needs to be manually applied to several objects.
2. Simulation of SCOPE statements in DAX
One of the most powerful features of Analysis Services Multidimensional is the MDX SCOPE statement, which is not available in Tabular. Using SCOPE allows us to change the context over which MDX expressions are evaluated. One obvious usage is the DateTool time intelligence pattern (http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx). Today, in Tabular, we can achieve a similar thing using a Tool dimension, but the downside is that we need to add calendar filtering logic within every single measure. If we h