Forum Discussion
Concatenate rows based on a shared ID using DAX
I have a table with data as below (representative only):
| ID | Organism | Organism type | Country |
| 1001 | A | plant | USA |
| 1001 | A | plant | Canada |
| 1001 | A | plant | Mexico |
| 1002 | B | insect | Germany |
| 1002 | B | insect | France |
| 1003 | C | bacteria | China |
| 1004 | D | virus | Australia |
and I want to display a table combining the countries rows so that there is only one entry for each ID, as so:
| ID | Organism | organism type | Country |
| 1001 | A | plant | USA, Canada, Mexico |
| 1002 | B | insect | Germany, France |
| 1003 | C | bacteria | China |
| 1004 | D | virus | Australia |
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:
| ID | Organism | Organism type | Country |
| 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
- amitchandakSuper User
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]," ")