Re-use of VAR between two MEASURES

[DoctorYSG]

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]
	)

[OwenAuger]

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?

[DoctorYSG]

@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:

[OwenAuger · Accepted solution]

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:

1. Create a dummy measure such as Dummy = BLANK ( )
2. Using Tabular Editor (or TMDL view) set the Dummy measure's Detail Rows Expression to the desired table expression.
3. 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?