topic Re: How to create a custom table or Matrix table in DAX Commands and Tips

How to create a custom table or Matrix table

Abbi — Wed, 02 Feb 2022 08:23:20 GMT

Hi All,

Looking forward for your help.

I have 2 tables, Table1 and Table 2.

Table1 -

ID	Depedency	Owner
1234	Client	Maruti
1234	Testing	Maruti
1234	Engg	Maruti
8125	Support	Varun M
6542	Testing	Mike
6542	Support	Mike
6542	Reporting	Mike
6542	Client	Mike

Table2 -

ID	Depedency
1234	Client
1234	Engg
6542	Reporting
6542	Engg
8125	Client
8125	Engg
8125	Support
9654	Client

I need to create visual like below: Not sure how can I achieve through a Table or Matrix table.

ID	T1.Dependency	T2.Dependency	Owner
1234	Client	Client	Maruti
	Testing	Engg
	Engg
8125	Support	Client	Varun M
		Engg
		Support
6542	Testing	Reporting	Mike
	Support	Engg
	Reporting
	Client
9654		Client

Please help.

Re: How to create a custom table or Matrix table

ValtteriN — Wed, 02 Feb 2022 08:57:18 GMT

Hi,

I recommend approaching this a bit differently. With these steps you can visualize the information you want quite well.
You can create a dimension table like this one:

and then use it to create relationships:

By doign this you can create the visual like this:
Use the dimension table for slicer and then just create two table visuals.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: How to create a custom table or Matrix table

Abbi — Wed, 02 Feb 2022 13:04:51 GMT

Thanks ValtteriN for the post, your post doesnt solve my issue, as my clients export the data to csv, they dont want 2 tables.

If you have any better view other than table please suggest, where i can show them the difference in the dependency against the ID's

Re: How to create a custom table or Matrix table

Icey — Fri, 11 Feb 2022 10:14:06 GMT

Hi Abbi ,

Please check this:

Detail steps:

1. Create two tables.

ID = DISTINCT ( UNION ( DISTINCT ( Table1[ID] ), DISTINCT ( Table2[ID] ) ) )

Depedency = DISTINCT ( UNION ( DISTINCT ( Table1[Depedency] ), DISTINCT ( Table2[Depedency] ) ) )

2. Create relationships.

3. Create a Matrix.

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to create a custom table or Matrix table

tamerj1 — Fri, 11 Feb 2022 11:25:28 GMT

Hi Abbi ,
Here is the sample file with solution: https://www.dropbox.com/t/BBz5quuploWDDOAY

This is very similar to this post https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Compare-two-columns-from-2-different-tables/m-p/2328322#M58397
Already ansewed your query but looks like you forgot to mark my answer as accepted 🙂

You can use power query to generate a distinct table of all ID's as described in the other post.
Code for Table 1 & 2:

T(n) Dependancy = IF ( HASONEVALUE ( Append1[ID] ), CONCATENATEX ( Table(n), Table(n)[Depedency], "," & UNICHAR(10) ) )

For "Owner" you just need to retrieve the VALUES of the culumn "Owner" as follows

Owner = IF ( HASONEVALUE ( Append1[ID] ), DISTINCT ( VALUES ( Table1[Owner] ) ) )

Your report would look like this

Kindly requesting you to mark both solutions as "Accepted".
Thank you and have a great day

Re: How to create a custom table or Matrix table

tamerj1 — Fri, 11 Feb 2022 11:59:37 GMT

Abbi
If you're not comfortable with using Power Query, you can create a calculated table using this code:

Unique ID's = VAR Table_1 = SELECTCOLUMNS ( Table1, "ID", Table1[ID] ) VAR Table_2 = SELECTCOLUMNS ( Table2, "ID", Table2[ID] ) VAR Result = DISTINCT ( UNION ( Table_1, Table_2 ) ) RETURN Result