Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
WinterMist
Impactful Individual
Impactful Individual

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.

 

WinterMist_0-1701356608648.png

 

Thanks in advance.

 

Regards,

Nathan

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

2 REPLIES 2
WinterMist
Impactful Individual
Impactful Individual

@AlexisOlson - Thanks very much for the help!

AlexisOlson
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors