topic Re-use of VAR between two MEASURES in DAX Commands and Tips

Re-use of VAR between two MEASURES

DoctorYSG — Tue, 17 Jun 2025 18:43:23 GMT

I am having to duplicate the calculation of a Summarize table between two measures. I tried putting the VAR outside of both, then I loose the Filter context when using the Measure. Only when the VAR is in both measures does this work properly. It would be nice to simplify this.

DEFINE MEASURE 'Measure Table'[TokenCount] = VAR cTable = FILTER( ADDCOLUMNS( SUMMARIZE( 'Traces', 'Traces'[CorrID], 'Traces'[DateStamp] ), "X", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Microsoft Outlook" ), "Y", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "AAD Token Broker Plugin" ) ), NOT (ISBLANK([X])) && NOT (ISBLANK([Y])) ) RETURN COUNTROWS(cTable) MEASURE 'Measure Table'[rawCorrToken] = VAR cTable = FILTER( ADDCOLUMNS( SUMMARIZE( 'Traces', 'Traces'[CorrID], 'Traces'[DateStamp] ), "X", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Microsoft Outlook" ), "Y", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "AAD Token Broker Plugin" ) ), NOT (ISBLANK([X])) && NOT (ISBLANK([Y])) ) VAR Count_Items = COUNTROWS(cTable) VAR Sum_X = SUMX( cTable, [X] ) VAR Sum_X2 = SUMX( cTable, [X] ^ 2 ) VAR Sum_Y = SUMX( cTable, [Y] ) VAR Sum_Y2 = SUMX( cTable, [Y] ^ 2 ) VAR Sum_XY = SUMX( cTable, [X] * [Y] ) VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_X * Sum_Y VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X ^ 2 VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y ^ 2 VAR Pearson_Denominator = SQRT(Pearson_Denominator_X * Pearson_Denominator_Y) VAR TokenCorr = DIVIDE( Pearson_Numerator, Pearson_Denominator ) RETURN TokenCorr EVALUATE ADDCOLUMNS( SUMMARIZE( 'Traces', 'Traces'[BaseName] ), "Correlation", [rawCorrToken], "Count", [TokenCount] )

Re: Re-use of VAR between two MEASURES

OwenAuger — Tue, 17 Jun 2025 21:37:51 GMT

Hi DoctorYSG

I'm assuming you require a self-contained DAX query.

You don't necessarily need to define measures within the query. You could use GENERATE instead of ADDCOLUMNS, which allows the extension columns to reference common variables. You can then evaluate cTable as one of those variables, and make use of LINESTX to simplify the code.

For example, you could write this query:

EVALUATE GENERATE ( SUMMARIZE ( 'Traces', 'Traces'[BaseName] ), VAR cTable = CALCULATETABLE ( FILTER ( SELECTCOLUMNS ( SUMMARIZE ( 'Traces', 'Traces'[CorrID], 'Traces'[DateStamp] ), "X", CALCULATE ( AVERAGE ( 'Traces'[ResponseTime] ), 'Traces'[AppName] = "Microsoft Outlook" ), "Y", CALCULATE ( AVERAGE ( 'Traces'[ResponseTime] ), 'Traces'[AppName] = "AAD Token Broker Plugin" ) ), NOT ( ISBLANK ( [X] ) ) && NOT ( ISBLANK ( [Y] ) ) ) ) VAR TokenCount = COUNTROWS ( cTable ) VAR LeastSquaresResult = LINESTX ( cTable, [Y], [X] ) VAR rawCorrToken = SQRT ( SELECTCOLUMNS ( LeastSquaresResult, [CoefficientOfDetermination] ) ) RETURN ROW ( "Correlation", rawCorrToken, "Count", TokenCount ) )

Would a query like this work for you?

Re: Re-use of VAR between two MEASURES

DoctorYSG — Tue, 17 Jun 2025 22:00:08 GMT

OwenAuger

A very reasonable assumption. I should have given more context for the problem I am trying to solve. I am trying to compare multiple time series (streams) for correlation. So, no, it is not a self-contained measure, but rather one that can be embedded in different filter contexts. I want both the Pearson's Correlation and the Count (I will work on Spearman's later which I am told is better for non-linear).

In any case when the measure is in chart, it looks like this:

Re: Re-use of VAR between two MEASURES

OwenAuger — Wed, 18 Jun 2025 02:29:04 GMT

Thanks DoctorYSG, understood 🙂
If the objective is to avoid repeating the code for the table expression across multiple measures, the only method I am aware of is by using the DETAILROWS function as described in this article:

https://www.sqlbi.com/articles/creating-table-functions-in-dax-using-detailrows/

Notes:

While this technique works, it uses DETAILROWS beyond what it is designed for.
The upcoming DAX user-defined functions feature will allow table-valued functions and would be a preferable technique once available 🙂

The basic idea with DETAILROWS is to:

Create a dummy measure such as Dummy = BLANK ( )
Using Tabular Editor (or TMDL view) set the Dummy measure's Detail Rows Expression to the desired table expression.
Where required in other measures, use DETAILROWS ( [Dummy] ) to return the table.

Sample Detail Rows Expression:

FILTER ( SELECTCOLUMNS ( SUMMARIZE ( 'Traces', 'Traces'[CorrID], 'Traces'[DateStamp] ), "X", CALCULATE ( AVERAGE ( 'Traces'[ResponseTime] ), 'Traces'[AppName] = "Microsoft Outlook" ), "Y", CALCULATE ( AVERAGE ( 'Traces'[ResponseTime] ), 'Traces'[AppName] = "AAD Token Broker Plugin" ) ), NOT ( ISBLANK ( [X] ) ) && NOT ( ISBLANK ( [Y] ) ) )

Would this work for you with your set of measures?