Forum Discussion

WinterMist's avatar
WinterMist
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

Unable to Reference VAR Table Columns

Hello Community - 

 

So I am aware that:

"a variable name cannot be used as a table name in a column reference"

(https://www.sqlbi.com/articles/table-and-column-references-using-dax-variables/)

 

However, since my attempts have failed (to use ADDCOLUMNS to create a VAR table in which columns can be referenced),

I have left the code for V2 & V3 wrong intentionally, simply to show what I am trying to do.

 

VAR V1 --CREATE TEMP TABLE FROM 6 ID'S

VAR V2 --GET DISTINCT LIST OF [Crop Abbrev] FROM V1

VAR V3 --CREATE COMMA DELIMITED LIST OF DISTINCT [Crop Abbrev] FROM V2

 

Desired Result of V3 = "CORN, SOYBEANS"

 

Of course, currently only V1 works, which is why I am returning it here.

 

 

Thanks in advance.

 

Regards,

Nathan

  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    One option is to use v1 as a filter table like this:

    VAR v1 =
    FILTER (
        ALL ( 'D Crop' ),
        'D Crop'[CropKey] IN { 5767, 6014, 5408, 5769, 6015, 5438 }
    )
VAR v2 = CALCULATETABLE ( DISTINCT ( 'D Crop'[Crop Abbrev] ), v1 )
VAR v3 =
    CONCATENATEX (
        v2,
        'D Crop'[Crop Abbrev], ", ",
        'D Crop'[Crop Abbrev], ASC
    )
EVALUATE
    v3

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    One option is to use v1 as a filter table like this:

    VAR v1 =
    FILTER (
        ALL ( 'D Crop' ),
        'D Crop'[CropKey] IN { 5767, 6014, 5408, 5769, 6015, 5438 }
    )
VAR v2 = CALCULATETABLE ( DISTINCT ( 'D Crop'[Crop Abbrev] ), v1 )
VAR v3 =
    CONCATENATEX (
        v2,
        'D Crop'[Crop Abbrev], ", ",
        'D Crop'[Crop Abbrev], ASC
    )
EVALUATE
    v3