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

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  

2 ACCEPTED SOLUTIONS

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

15 REPLIES 15
AlB
Super User III
Super User III

@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 

SU18_powerbi_badge

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

Magic!  It works like a charm! 🎉

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
Solution Sage
Solution Sage

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.

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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)

@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
Super User II
Super User II

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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

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.

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.

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

 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors