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
Looking for exactly this!
RE: Add global variables to DAX
Great idea, will save a tone of effort
RE: Add global variables to DAX
Please add this functionality. We are getting crushed on performance because of duplication of effort that this would solve.
RE: Add global variables to DAX
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.
RE: Add global variables to DAX
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".
RE: Add global variables to DAX
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.
RE: Add global variables to DAX
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.
RE: Add global variables to DAX
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.
RE: Add global variables to DAX
great idea would be useful for my current project
RE: Add global variables to DAX
This idea is brilliant ! It would be such a major improvement to DAX !