Matrix visual without merging

[Anonymous]

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!

[Anonymous · Accepted solution]

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.

[Anonymous]

@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!

[amitchandak]

@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