Unexpected behaviour of VAR

[schanuleke]

While trying to find the best (quickest and/or most efficient) way to do a "COUNT(*) GROUP BY' type of operation in DAX on a temporary table (constructed from other tables), I've noticed some strange behaviour when using VAR to define this temporary table.

To do this operation, one could simply define a New Table "TempTable", and use that new table as a temporary table in the grouping operation:

TempTable = 
UNION(
    'Table1';
    'Table2';
    'Table3'
)

ResultTable = 
ADDCOLUMNS(
    SUMMARIZE(
        TempTable;
        [Identifier]
    );
    "Count"; CALCULATE(COUNTROWS(TempTable))
)

This indeed gives me the result I'm looking for:

Identifier  Count
=================
136         3
723         1
529         1
953         2
128         3
[..]

However, I'd rather not define the temporay tables in this way, since I'll need MANY of them. In my opinion, they slow things down and mess up the FIELDS pane.

Instead, I would like to use a VAR table as temporary table, for every resulting table:

ResultTable = 

VAR TempTableVar = 
UNION(
    'Table1';
    'Table2';
    'Table3'
)

RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            TempTableVar;
            [Identifier]
        );
        "Count"; CALCULATE(COUNTROWS(TempTableVar))
    )

But this gives me a different, incorrect result:

Identifier  Count
=================
136         6452
723         6452
529         6452
953         6452
128         6452
[..]

The number shown now in the [Count] column is always the same for every row. It's the total number of rows for the whole TempTableVar, which of course is not what I want.

I've also tried the "hybrid" solution:

VAR TempTableVar = TempTable

RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            TempTableVar;
            [Identifier]
        );
        "Count"; CALCULATE(COUNTROWS(TempTableVar))
    )

But this gives the same wrong result. So it seems the "problem" exists with the VAR functionality in DAX.

Anybody who can explain what's going on here with VAR?

[v-ljerr-msft · Accepted solution]

Hi @schanuleke,

ResultTable = 

VAR TempTableVar = 
UNION(
    'Table1';
    'Table2';
    'Table3'
)

RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            TempTableVar;
            [Identifier]
        );
        "Count"; CALCULATE(COUNTROWS(TempTableVar)) // the TempTableVar do not change, or be filtered by context
    )

The VAR function stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

So you should use the formula below instead. 

ResultTable2 =
VAR TempTableVar =
    UNION ( 'Table1', 'Table2', 'Table3' )
RETURN
    SUMMARIZE (
        TempTableVar,
        [Identifier],
        "Count",
        VAR i = [Identifier]
        RETURN
            COUNTROWS ( FILTER ( TempTableVar, [Identifier] = i ) )
    )

Regards

[schanuleke]

@v-ljerr-msft by the way, I also came up with a workaround myself, which doesn't use a VAR statement:

ResultTable = 
GROUPBY(
    ADDCOLUMNS(
        UNION(
            'Table1';
            'Table2';
            'Table3'
        );
        "Integrator";
        1
    );
    [Identifier];
    "Count";
    SUMX(
        CURRENTGROUP();
        [Integrator]
    )
)

This also works. But I don't really like to use those workarounds.

Would you know of a way to test which solution is the most efficient / fastest?

[schanuleke]

Oh wow, I tried it and yes this works! 

I had also noticed this sentence, "Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression." but I didn't really understand it (What exactly is meant by "a variabele expression"???). But now I do! Thanks!

[Greg_Deckler]

You have a bunch of extraneous stuff in your calculation but I simplified it down to this and it still does not work correctly:

ResultTable1 = 

VAR TempTableVar = 
UNION(
    'Table1',
    'Table2',
    'Table3'
)

RETURN
    SUMMARIZE(
            TempTableVar,
            [Identifier],
        "Count", COUNTROWS(TempTableVar)
    )

Weird part is, if you RETURN the TempTableVar, the table being returned is correct.