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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX: FILTER and CONCATENATE UNIQUE values

 

I am trying to FILTER/LOOKUPVALUE and CONCATENATE the relevant UNIQUE values but right now, I am unable to get it to work. I can only do it without the values being UNIQUE.

My current DAX formula:
CONCATENATEX (
FILTER (
ALL ( Table2 ),
Table2[Column1] = Table1[Column1]
),
Table2[Column2],
", "
)

The output from here goes like:
Value1, Value2, Value2

My desired output is: (without any of the values being duplicated)
Value1, Value2
1 ACCEPTED SOLUTION

@Anonymous , Assuming this is a new column, I tried the same on my data, a new column. without all

Column = CONCATENATEX(summarize(FILTER(Sales,Sales[Item ID] = 'Item'[Item Id]), Sales[City Id]),[City Id], ",")

 

amitchandak_0-1641470019112.png

 

View solution in original post

5 REPLIES 5
smpa01
Super User
Super User

@Anonymous  provide sample data and expected output to accelerate response

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
amitchandak
Super User
Super User

@Anonymous , try like

 

CONCATENATEX (
summarize(FILTER (
ALL ( Table2 ),
Table2[Column1] = cvShipments[Column1]
),
Table2[Column2]),
", "
)

Anonymous
Not applicable

@amitchandak Hi there, thanks for commenting. I tried but the output that I've obtained is "," and ", ,"

Seems like only commas are being concatenated here...

@Anonymous , Assuming this is a new column, I tried the same on my data, a new column. without all

Column = CONCATENATEX(summarize(FILTER(Sales,Sales[Item ID] = 'Item'[Item Id]), Sales[City Id]),[City Id], ",")

 

amitchandak_0-1641470019112.png

 

Anonymous
Not applicable

@amitchandak This works great, thanks. Think in the first comment you provided, there was a missing Table2[Column2] as part of the CONCATENATEX formula, hence the error. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors