cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

DAX: Is it possible to refer to columns of a table variable?

Hi all,

 

If we define a variable as a table, can we later refer to the columns in that variable? For instance, we have the following code:

 

VAR TableVar =
    SUMMARIZECOLUMNS ( 'Product'[ProductKey], 'Product'[Color] )
RETURN
    COUNTROWS(CALCULATETABLE ( TableVar, TableVar[ProductKey] = 1 ))

where we are trying to filter TableVar. We need to be able to refer to its columns but the code throws an error ("Cannot find table TableVar").

Can this be done at all in DAX? If so, how?

 

Many thanks  

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX: Is it possible to refer to columns of a table variable?

Hey @AlB,

 

basically it's possible to access table variables or columns from that table, but this is not as simple as one may think. If you need more information you have to search / google for "DAX data lineage".

 

Here is a simple example how to "count" the rows of a table, using a combination of ADDCOLUMNS(SUMMARIZE(...)) to create a variable, and finally SUMX to iterate over the tablevariable and a column from the table just as an expression.

 

Measure 2 = 
VAR TableVar =
    ADDCOLUMNS(
        SUMMARIZE (
            'Product'
            ,'Product'[ProductKey]
            ,'Product'[Color]
        )
        ,"aColumn", 1
    )
RETURN
    SUMX(
        TableVar
        ,[aColumn]
    )

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
7 REPLIES 7
AkhilAshok Established Member
Established Member

Re: DAX: Is it possible to refer to columns of a table variable?

A variable is actually a Constant. You cannot change it once it is stored. In addition, you cannot refer a column from a variable table like TableVar[ProductKey].

What exactly are you trying to achieve? Maybe there is an easier way to do this. Are u trying to count the unique colors of a Product? It doesn't makes sense as there will be only one color.

Super User
Super User

Re: DAX: Is it possible to refer to columns of a table variable?

Hey @AlB,

 

basically it's possible to access table variables or columns from that table, but this is not as simple as one may think. If you need more information you have to search / google for "DAX data lineage".

 

Here is a simple example how to "count" the rows of a table, using a combination of ADDCOLUMNS(SUMMARIZE(...)) to create a variable, and finally SUMX to iterate over the tablevariable and a column from the table just as an expression.

 

Measure 2 = 
VAR TableVar =
    ADDCOLUMNS(
        SUMMARIZE (
            'Product'
            ,'Product'[ProductKey]
            ,'Product'[Color]
        )
        ,"aColumn", 1
    )
RETURN
    SUMX(
        TableVar
        ,[aColumn]
    )

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: DAX: Is it possible to refer to columns of a table variable?

Thanks @TomMartens

Interesting. So i've been playing a bit and it seems that it can be used under some circumstances, mostly with iterators.

This for instance works:

 

VAR TableVar =
    SUMMARIZECOLUMNS ( 'Product'[ProductKey], 'Product'[Color] )
RETURN
    MAXX ( TableVar, [ProductKey] )

but both of the following throw an error:

 

 

VAR TableVar =
    SUMMARIZECOLUMNS ( 'Product'[ProductKey], 'Product'[Color] )
RETURN
    MAX ( [ProductKey] )
VAR TableVar =
    SUMMARIZECOLUMNS ( 'Product'[ProductKey], 'Product'[Color] )
RETURN
    MAX ( TableVar[ProductKey] )

 

 

 

 

Super User
Super User

Re: DAX: Is it possible to refer to columns of a table variable?

Thanks @AkhilAshok

Sure, that code does not make much practical sense. It was just to show what I was looking for. 

Two reasons for the question:

1. To make the code more readable if I have a complex table I am going to operate on. A simple example:

VAR Var=Complex_Table_Expression

and then

FILTER(Var, ...) 

so that I do not have to write the full expression within FILTER.

 

2. When I genrate a table and then want to operate directly on it to extract some info

VAR Var=Complex_Table_Expression

and then for instance I want to extract the MAX of one of the columns

 

Thank you

Super User
Super User

Re: DAX: Is it possible to refer to columns of a table variable?

@TomMartens, @AkhilAshok

a follow-up question

 

Imagine we have something like this

 

CALCULATE(COUNTROWS('Sales'), FILTER('Product', [Some Measure]>0))

and I am trying to debug it. I have some doubts with how FILTER(..) and filter context interact and I would like to see what the result of the bit in red is to better understand what is going on. Ideally, I would assign that to a variable while the whole expression is being executed and check it later, as you can do in other programming languages. Can something like this be done in DAX?

 

 

Many thanks  

AkhilAshok Established Member
Established Member

Re: DAX: Is it possible to refer to columns of a table variable?

Multiple options:

 

1. Use DAX Studio to connect to your Power BI Desktop model and execute the bit in red and see the results. 

OR

2. Create a new table in Power BI Desktop itself with the red query and see the output.

 

In both cases, if you have additional slicers/filters in the visual, then use CALCULATETABLE around your query to manually pass all those filters.

Super User
Super User

Re: DAX: Is it possible to refer to columns of a table variable?

Hi @AlB

looks like one can reference a column from a table that's defined by a variable only with functions where the name of the column is a sepate argument ( a bit like in M, where you can use Table.Column(<TableName>, <ColumnName>) and use variables for table as well as column name, whereas TableName[ColumnName] cannot be used with variables). So all the X-functions will work here.

 

I've created a measure that helps debugging CALCULATE-measures here: https://www.thebiccountant.com/2019/05/19/dax-calculate-debugger/  - maybe it's useful for your case.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries