Forum Discussion
Creating Column in Table View Combining Two Tables with same ids but one with duplicates
I have two tables as depicted in the visuals here. As you can see table 2 has a mix of blanks and additional entries. I need table A exactly as is but with the blanks removed and additional entries from table 2 for the same id separated by commas as shown.
Preferably via DAX.
TIA
Needed table
| 1 | IA,XU |
| 2 | PG,GTM |
| 3 | XU |
| 4 | XU |
| 5 | GTM,IA |
Table 1
| 1 | IA |
| 2 | PG |
| 3 | XU |
| 4 | XU |
| 5 | GTM |
Table 2
| XU | |
| PG | |
| 1 | IA |
| 1 | XU |
| 2 | PG |
| 2 | GTM |
| 3 | XU |
| 4 | XU |
| 5 | GTM |
| 5 | IA |
3 Replies
Anonymous Hi try it 2 calculate table
CombinedTable = FILTER ( UNION ( SELECTCOLUMNS ( 'Table 1', "ID", 'Table 1'[Col1], "Value", 'Table 1'[Col2] ), SELECTCOLUMNS ( 'Table 2', "ID", 'Table 2'[Col1], "Value", 'Table 2'[Col2] ) ), NOT ISBLANK ( [ID] ) )and
NeededTable = SUMMARIZE ( CombinedTable, CombinedTable[ID], "CombinedValues", CONCATENATEX ( VALUES ( CombinedTable[Value] ), CombinedTable[Value], "," ) )Anonymous Hi, Show your DAX code
