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

[AlB]

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  

[TomMartens · Accepted solution]

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

[FreemanZ]

Once stuck by this topic for quite a while, mostly further calculation after ADDCOLUMNS.

The replies in this post help a lot and the following article as well:

https://prologika.com/referencing-columns-in-dax-table-variables/

[AlB]

@fenixen 

Yeah, that's what the code I've suggested does.

The second argument in the CALCULATE in your code is:

TableFaktGradPerAnsatt<0.5

 so with this you are actually checking whether the full table is <0.5. That is what I meant

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @fenixen 

1. Variables in DAX are immutable after declaration so CALCULATE will not effect any change on TableFaktGradPerAnsatt

2. You were checking whether a whole table is < 0.5?

Try this:

00 Test Antall ledige :=
VAR TableFaktGradPerAnsatt =
    ADDCOLUMNS (
        SUMMARIZE (
            'dim Organization',
            'dim Organization'[AnsattNr],
            "FG%", [Faktureringsgrad %]
        ),
        "Test", [Faktureringsgrad %]
    )
RETURN
    COUNTX ( FILTER ( TableFaktGradPerAnsatt, [Test] < 0.5 ), [Test] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[fenixen]

Magic!  It works like a charm! 🎉

[fenixen]

Hi @AlB 

Not the whole table, i want to check how many employees have Fakturerinsgrad % value less than 0.5 
Count the number of rows below 0.5 

Edit: Testing your formula now! 

[AkhilAshok]

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.

[AlB]

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

[TomMartens · Accepted solution]

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

[bwilkey]

Would rather see code that Does Not reference the column name.  More useful.

[EricShahi]

Hi thanks for your Tip, I have followed your method and works fine to some extent.

In your example the measure will return sum of aColumn. Thats works okay.

Lets say we bring this measure visually  into a table and if want to group by product colour then power bi gives us an error. 

[fenixen]

Hi Tom 

In your Example you sum the 1 values in "aColumn". 
Is it possible to do a "sumif" on the column?  

I only want to sum the values below 0.5 in my column. 

I tried using =CALCULATE() somehow.. but arent able to get it to work. 

00 Test Antall ledige :=
VAR TableFaktGradPerAnsatt =
    ADDCOLUMNS (
        SUMMARIZE (
            'dim Organization',
            'dim Organization'[AnsattNr],
            "FG%", [Faktureringsgrad %]
        ),
        "Test", [Faktureringsgrad %]
    )
RETURN
    CALCULATE(COUNTX ( TableFaktGradPerAnsatt, [Test] ),TableFaktGradPerAnsatt<0.5)

[AlB]

@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  

[ImkeF]

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.

[micwebnet]

This seems inconsistent to the point of being a bug in DAX. Many run into this expecting column reference to work on the var table, and it indeed does for iterator X operators, but not for non-iterator. Question is what makes X operators different in this context, and why can't non-X iterators be made to access var table column references the same way.

As was pointed out, VAR is incredibly useful to make DAX queries readable.

Interestingly enough iterator functions working with var table column references isn't mentioned in https://www.sqlbi.com/articles/table-and-column-references-using-dax-variables/.

I had a very legit example where I needed to break out filtered calculated table into a VAR so I could reference it twice, once for count, and once for actual column reference. MAXX came to the rescue, after numerous threads saying it's not possible. Here's the query:

Stage =

VAR

__DateFirstUsed = IF(ISBLANK([Service start date]), [Date], [Service start date])

VAR

__FilteredPropertyStages = FILTER(PropertyStages,

PropertyStages[Asset] = HD_AnnotatedExpenseDetails[Asset] &&

PropertyStages[Start date] <= __DateFirstUsed &&

PropertyStages[End date] >= __DateFirstUsed)

VAR

__Stage = SWITCH(COUNTROWS(__FilteredPropertyStages),

BLANK(), "Asset not specified",

1, MAXX(__FilteredPropertyStages, [Stage]),       /* <== this didn't work with just MAX(__FilteredPropertyStages[Stage]) */

"More than one property stage match")

RETURN

__Stage

Best,  Michael

[jhimelic]

did you ever find a way to have this work w/o the use of iterator expressions (e.g. maxx)? i need to use this for percentile and of course it is not available in the percentilex version

thanks

[Anonymous]

I headed into the same issue just now and the error message was clear enough to say Max function accepts columnreference only, even if you table variable has one column only, which is still considered as a table not column. As for why the non-X functions not working here. I think still the two contexts play a key role in this. Strictly speaking, non-X functions are not iterators and table variables are just logical tables, which might suggest they cannot skip iteration and give you the max or min value you want. Well, this is just my guess though.

[AkhilAshok]

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.

[AlB]

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