With the following DAX formula I concatenate all unique values of Column3
Column4 = CONCATENATEX( VALUES(Table1[Column3]),Table1[Column3],", ")
with result
ABC,DEF,WXYZ,HHT
Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
With the following DAX formula I concatenate all unique values of Column3
Column4 = CONCATENATEX( VALUES(Table1[Column3]),Table1[Column3],", ")
with result
ABC,DEF,WXYZ,HHT
I would like to concatenate all unique values, except the value "WXYZ"
I've tried this:
Column4 = CALCULATE( CONCATENATEX( VALUES(Table1[Column3]),Table1[Column3],", "), FILTER ( Table1, FIND( "WXYZ", Table1[Column3],, 0 ) = 0 ) )
But I get circular dependency was detected:
My expected output would be:
ABC,DEF,HHT
UPDATE
Below from ID to Column3 is my input table and I'd like to get the unique values (excluding "NOT AVAILABLE") in Column4 for each ID(0,1,2,3) and each value in Column2 (A and B). So in Column4 (output column) would look like this:
ID | Column1 | Column2 | Column3 | Column4 |
0 | 51234 | B | NOT AVAILABLE | KTGGTG |
1 | 77567 | A | HWUS | HWUS,TKYHNY |
2 | 123149674 | A | HCGUSG | HCGUSG,TKYHNY |
2 | 14047259 | A | HCGUSG | HCGUSG,TKYHNY |
3 | 71369756 | A | RCNGBN | RCNGBN |
0 | 502609 | B | KTGGTG | KTGGTG |
0 | 1312314 | A | HCGUSG | HCGUSG |
1 | 775 | A | TKYHNY | HWUS,TKYHNY |
2 | 775 | A | TKYHNY | HCGUSG,TKYHNY |
3 | 1989707 | B | UT1TY1 | HWUS,KTGGTG,UT1TY1 |
3 | 532715 | A | RCNGBN | RCNGBN |
3 | 502991 | B | KTGGTG | HWUS,KTGGTG,UT1TY1 |
3 | 120631 | B | HWUS | HWUS,KTGGTG,UT1TY1 |
3 | 1320334 | B | NOT AVAILABLE | HWUS,KTGGTG,UT1TY1 |
3 | 31234 | B | UT1TY1 | HWUS,KTGGTG,UT1TY1 |
3 | 47259 | B | KTGGTG | HWUS,KTGGTG,UT1TY1 |
Thanks in advance for any help.
Solved! Go to Solution.
@cgkas -
Here's the best I could come up with:
Create a table as:
Table = SUMMARIZE ( Table1, Table1[ID], Table1[Column2], "temp", CONCATENATEX ( FILTER ( DISTINCT(Table1[Column3]), FIND ( "NOT AVAILABLE", Table1[Column3], , 0 ) = 0 ), Table1[Column3], ", " ) )
Column4 as:
Column4 = LOOKUPVALUE( 'Table'[temp], 'Table'[ID],Table1[ID], 'Table'[Column2],Table1[Column2] )
Produces:
Proud to be a Super User!
@cgkas -
This seems to work:
Column5 = VAR _filter = FILTER ( Table1, FIND ( "WXYZ", Table1[Column3], , 0 ) = 0 ) RETURN CONCATENATEX ( _filter, Table1[Column3], ", " )
Proud to be a Super User!
Hello ChrisMendoza, Thanks for your help. I'm receiving as output all values, not only unique ones. I'm testing in DAX Studio in Excel 2016.
@cgkas -
I presumed your sample table looked like the below, is that not the case?:
Please provide an accurate sample.
Proud to be a Super User!
@ChrisMendoza wrote:@cgkas -
I presumed your sample table looked like the below, is that not the case?:
Please provide an accurate sample.
@ChrisMendoza wrote:@cgkas -
I presumed your sample table looked like the below, is that not the case?:
Please provide an accurate sample.
Hi @ChrisMendoza,
I've updated my original post showing a sample table and output.
Thanks for the help.
@cgkas -
Here's the best I could come up with:
Create a table as:
Table = SUMMARIZE ( Table1, Table1[ID], Table1[Column2], "temp", CONCATENATEX ( FILTER ( DISTINCT(Table1[Column3]), FIND ( "NOT AVAILABLE", Table1[Column3], , 0 ) = 0 ), Table1[Column3], ", " ) )
Column4 as:
Column4 = LOOKUPVALUE( 'Table'[temp], 'Table'[ID],Table1[ID], 'Table'[Column2],Table1[Column2] )
Produces:
Proud to be a Super User!
@ChrisMendoza wrote:@cgkas -
Here's the best I could come up with:
Is the best you could do, but is the solution I was looking for heheh. Thanks so much for the help.
Can you filter in CONCATENATEX
Column4 = CALCULATE( CONCATENATEX( FILTER ( Table1, FIND( "WXYZ", Table1[Column3],, 0 ) = 0 ),Table1[Column3],", "), )
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information.
Thanks.
@amitchandak wrote:
Column4 = CALCULATE( CONCATENATEX( FILTER ( Table1, FIND( "WXYZ", Table1[Column3],, 0 ) = 0 ),Table1[Column3],", "), )
There's an extra comma giving you the error message.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |