Forum Discussion
Helper IRELATIONS IN POWER BI
Dear All, i need your help to understand...
I have to tables (simple tables):
TAB A
| NOME | CITTA |
| CARLOTTA | PISA |
| FEDERICA | FIRENZE |
| MIMMO | NAPOLI |
| PIERO | NAPOLI |
| MARCO | MILANO |
TAB B
| NOME | SCUOLA |
| IDA | DINI |
| CARLOTTA | FIBONACCI |
| SALVATORE | FERMI |
| FEDERICA | FERMI |
| MIMMO | DINI |
| GIOVANNI | BUONARROTI |
I would like to realize a table like this:
| NOME | CITTA | SCUOLA |
| CARLOTTA | PISA | FIBONACCI |
| FEDERICA | FIRENZE | FERMI |
| MIMMO | NAPOLI | DINI |
| PIERO | NAPOLI | |
| MARCO | MILANO | |
| IDA | DINI | |
| SALVATORE | FERMI | |
| GIOVANNI | BUONARROTI |
Is it possible? How?
Thank you very much for your help.
Hey,
If you have access to Power Query, you can use Merge Queries, and use Full Outer Type.The result should be something like the above. To get the NOME column to look like your result, you can use a query like: Table.AddColumn(#"Expanded Tab B", "NOME (Keep)", each if [NOME] = null then [NOME.1] else [NOME]) in PowerQuery to a column without null in the NOME column.
If you want to do so in DAX, try something like:
MergedTable = ADDCOLUMNS( DISTINCT( UNION(SELECTCOLUMNS('Tab A', "NOME", 'Tab A'[NOME]), SELECTCOLUMNS('Tab B', "NOME",('Tab B'[NOME]))) ), "Citta", CALCULATE(MAX('Tab A'[CITTA]), 'Tab A'[NOME] = EARLIER([NOME])), "Scuola", CALCULATE(MAX('Tab B'[SCUOLA]), 'Tab B'[NOME] = EARLIER([NOME])) )
3 Replies
vicky_Super User
Hey,
If you have access to Power Query, you can use Merge Queries, and use Full Outer Type.The result should be something like the above. To get the NOME column to look like your result, you can use a query like: Table.AddColumn(#"Expanded Tab B", "NOME (Keep)", each if [NOME] = null then [NOME.1] else [NOME]) in PowerQuery to a column without null in the NOME column.
If you want to do so in DAX, try something like:
MergedTable = ADDCOLUMNS( DISTINCT( UNION(SELECTCOLUMNS('Tab A', "NOME", 'Tab A'[NOME]), SELECTCOLUMNS('Tab B', "NOME",('Tab B'[NOME]))) ), "Citta", CALCULATE(MAX('Tab A'[CITTA]), 'Tab A'[NOME] = EARLIER([NOME])), "Scuola", CALCULATE(MAX('Tab B'[SCUOLA]), 'Tab B'[NOME] = EARLIER([NOME])) )
