Add global variables to DAX

[scott54]

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?

[do1]

'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

[amadeann]

I am looking forward for this feature as well.

[michiel_rozema]

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.

[tristan_malher1]

This idea is brilliant ! It would be such a major improvement to DAX !

[cstanke_skit]

great idea would be useful for my current project

[tristan_malher1]

Where are we now ? I can see there is this edit variables button in Power BI Section - but apparently it is only for SAP BW's.

[jakob_kornum]

And with bookmarks and drillthrough features that only work in fixed filter contexts. This idea is more valuable than ever before.. Please me this a priority.

[eugene_niemand]

Please provide some form of encapsulation similar to stored procs or dynamic sql. I have the following dax: SUMX ( VALUES ( 'Calendar'[YearMonthNumber] ), IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Calendar'[Date] ) ) ) = CALCULATE ( VALUES ( 'Calendar'[MonthDays] ) ), // Full month selected. CALCULATE ( [GP Margin], ALL ( 'Calendar' ), FILTER ( ALL ( 'Calendar'[YearMonthNumber] ), 'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) - 1 ) ), // Partial month selected. CALCULATE ( [GP Margin], ALL ( 'Calendar' ), CALCULATETABLE ( VALUES ( 'Calendar'[MonthDayNumber] ) ), FILTER ( ALL ( 'Calendar'[YearMonthNumber] ), 'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) - 1 ) ) ) ) and this is repeated for 18 measures and 3 granularities i.e. Monthly, Quarterly and Yearly. I have 18 other expressions for Daily and Weekly. Then I have a further 18 x 5 different expressions for other measures. This is more than a 150 measures with some sort of duplication where they could all be satisfied with about 7 generic measures.

[fbcideas_migusr]

Would this also allow you to dynamically swap out an entire field in a Table or Visual? For instance, I have a visual (a Table) that has just two columns in it: Comments related to a ticket (Ideas[Idea]), and the name of the commenter. I only want those notes to appear when a single ticket is selected from a Slicer, and otherwise I want that Table to show nothing (i.e. for it to appear blank). Currently, I'm having to mash all those comments and commenter name into a string in order to dynamically display or supress it, using this: Comments = IF(HASONEFILTER(Ideas[Idea]), CONCATENATEX(Comments, Comments[Commenter] & ": " & UNICHAR(10) & Comments[Comment], REPT(UNICHAR(10),2) ),"") But Danial's comments make me think that I have another use case for a "Measure that can return a Table".

[scott31]

This is severely needed for performance reasons. Please Add this functionality. The performance gains via reuse would be incredible especially if we had a global table variable that was evaluated at runtime.