Forum Discussion
Matrix visual without merging
Hello
I have data in the format:
| ID | Country1 | Country2 |
| 1 | Y | |
| 2 | Y | |
| 3 | Y | Y |
| ID | Type |
| 1 | A |
| 2 | B |
| 3 | C |
How can I create a pivot visual that will show:
| Country1 | Country1 | Country2 | Country2 | |
| Y | (blank) | Y | (blank) | |
| Type | ||||
| A | ||||
| B | ||||
| C |
I would prefer to do so without merging the tables, just by specifying a data model relationship, is that possible?
Thanks!
Hi Anonymous ,
How about create a new pivot table by using DAX:
New Pivot Table = var _t1=SELECTCOLUMNS('Table',"ID",[ID],"Country Type","Country1","If Y",[Country1]) var _t2= SELECTCOLUMNS('Table',"ID",[ID],"Country Type","Country2","If Y",[Country2]) return UNION(_t1,_t2)And then build relationship between it and the second table:
Then create a Matric visual:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , I think This problem has been asked and I shared file. Sharing it again
Check operation in power query and mtrix on Page 2
amitchandak thanks but as I underline in bold letters, I have two separate tables and I do not want to merge them nor combine them in one table permanently but instead use data modelling to specify a relationship. Is there a solution for this?
Thanks!
Hi Anonymous ,
How about create a new pivot table by using DAX:
New Pivot Table = var _t1=SELECTCOLUMNS('Table',"ID",[ID],"Country Type","Country1","If Y",[Country1]) var _t2= SELECTCOLUMNS('Table',"ID",[ID],"Country Type","Country2","If Y",[Country2]) return UNION(_t1,_t2)And then build relationship between it and the second table:
Then create a Matric visual:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
