Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Concatenate rows based on a shared ID using DAX

I have a table with data as below (representative only):

 

IDOrganismOrganism typeCountry
1001AplantUSA
1001AplantCanada
1001AplantMexico
1002BinsectGermany
1002BinsectFrance
1003CbacteriaChina
1004DvirusAustralia

 

and I want to display a table combining the countries rows so that there is only one entry for each ID, as so: 

IDOrganismorganism typeCountry
1001AplantUSA, Canada, Mexico
1002BinsectGermany, France
1003CbacteriaChina
1004DvirusAustralia

I need to do this using DAX and not in PowerQuery because there is a different part of the report where the countries do need to be separate. 

 

I am trying to write a measure but can't get it to work. I've tried using various combinations of concateneatex, combinevalues, calculate, filter, selectcolumns... but the closest I've gotten is for it to spit out a one row table listing all countries without regard to the ID, like this: 

IDOrganismOrganism typeCountry
Total  USA, Canada, Mexico, Germany, France, China, Australia


I tried to modify this solution https://community.fabric.microsoft.com/t5/Desktop/Concatenate-text-stored-in-several-rows/m-p/3196154#M1076668 like so: 
CALCULATE(CONCATENATEX('Table','Table'[Country]," "),FILTER('Table','Table'[ID]=EARLIER('TABLE'[ID]))

but the EARLIER formula is giving an error.


This feels like it should have a simple solution but I can't crack it, thanks!

  • Anonymous , just create a measure like

     

    CONCATENATEX('Table','Table'[Country]," ")

     

     

    for the column you need

     

    CONCATENATEX(FILTER('Table','Table'[ID]=EARLIER('TABLE'[ID]),'Table'[Country]," ")

1 Reply

  • Anonymous , just create a measure like

     

    CONCATENATEX('Table','Table'[Country]," ")

     

     

    for the column you need

     

    CONCATENATEX(FILTER('Table','Table'[ID]=EARLIER('TABLE'[ID]),'Table'[Country]," ")