How to reference column of DAX variable?

Question

I have a DAX variable that contains a table. How can I reference a particular column in that variable?

For example, in the below command, the EVALUATE returns an error. But it works if I replace table1 with FactInternetSales (which is the name of the table which contains that column)

define var table1=FactResellerSales
EVALUATE ROW("a",COUNTBLANK(table1[SalesAmount]))

score 1 · Answer 1 · answered Nov 02 '20 at 10:39

In DAX you can't use a variable name as with tables that are defined in the model. What you can do is to use the original table names using the table variable as a filter. For instance like in the following code

DEFINE
    VAR tv1 =
        FILTER( Tab1, Tab1[F1] = "A" )
EVALUATE
ROW( "a", CALCULATE( COUNTBLANK( Tab1[Value] ), tv1 ) )

score 0 · Answer 2 · answered Jan 21 '21 at 18:55

In your DEFINE statement, use TABLE instead of VAR. Although a variable expression can contain a table, its usage isn't the same as an actual query table.

DEFINE
    TABLE table1 = FactResellerSales
EVALUATE
    ROW("a", COUNTBLANK(table1[SalesAmount]))

DEFINE DAX Statement

TABLE DAX Statement

How to reference column of DAX variable?

2 Answers2