DAX: please check if I understood the use of Variabes in DAX correctly

Question

can someone please check if my theoretical understanding of variables is correct?

Suppose that I have a table with 2 columns, Condition1 and Condition2. I want to count the rows for which Condition1 = 4, and Condition2 = Black.

Soppose then that I write a measure called Black, that creates a Table where all rows have Condition2 = "Black". For Example:

Black:= FILTER (Table, Condition2 = "Black")

And then I write the combined code using variables:

Black4_version1 = 
var B = [Black]
return = CALCULATE(
COUNTROWS(B),
Condition1 = 4)

Then this code will not work because DAX thinks that the variable B is a single number (because it's calling a measure and measure is by default seen as a single value?), even though I have created a measure that should have created a table.

But if I create the table within a variable itself, then DAX will know that it's a table and then it will work?

Black4_Version2 = 
var B = FILTER (Table, Condition2 = "Black")
return = CALCULATE(
COUNTROWS(B),
Condition1 = 4)

I'm asking this because I want to be 100% sure that I have understood the answer given here: DAX: please explain why this measure with a variable will not work also because I have been using variables already at work, so I will need to re-check all the dashboards that I have built and talk to my manager about screwing up a big time. So you could say that my job depends on this.

sergiom · Accepted Answer · 2020-11-29T10:27:49.147

1

Variables are to be considered constants even when they contain a table. In your Black4_Version2 measure, the CALCULATE() doesn't change the COUNTROWS(B) result, since it's counting the rows of a constant table and no filtering is happening.

Black4_Version2 =
VAR B =
    FILTER(
        Table,
        Condition2 = "Black"
    )
RETURN
    CALCULATE(
        COUNTROWS( B ),
        Condition1 = 4
    )

but you can iterate over a constant table, therefore FILTER works

Black4_Version3 =
VAR B =
    FILTER(
        Table,
        Condition2 = "Black"
    )
RETURN
    COUNTROWS(
        FILTER(
            B,
            Condition1 = 4
        )
    )

P.S. I used the pseudo-DAX sintax used in the answer, since instead of Condition1 = 4 a column reference like Table[Column1] = 4 should be used

edited Nov 29 '20 at 10:27

answered Nov 29 '20 at 10:21

sergiom

4,791
3
24
32

so a good rule of thumb is that vaariables: - can be used to create filtered tables, which then can be used to be iterated over in return statement (so to create a Filter part of the CALCULATE statement) - cannot be used to replace Expression over which a Filter is applied later, because the Filter will not work even if you use CALCULATE? @sergiom – Lya Nov 29 '20 at 14:19
1

Correct, also variables can be used as filter arguments in calculate expression. – sergiom Nov 29 '20 at 15:01
so if I understand it correctly, I could write a calculation where I 1st calculate a variable that gives a filtered table (using FILTER), then in same calculation write a 2nd variable that also uses FILTER using the result from 1st var instead of Table. and then write a return statement where I can write an expression that is iterated over the Table that results from Var2? – Lya Nov 30 '20 at 07:30
1

yes.maybe there are more efficient solutions, but it would work – sergiom Nov 30 '20 at 14:07
1

thank you, I think that I understand now (well, maybe not understand, but I now have a rule that I can learn from the top of my head and hopefully the understanding will follow with practice) – Lya Nov 30 '20 at 14:22

DAX: please check if I understood the use of Variabes in DAX correctly

1 Answers1