How to create a custom table or Matrix table

[Abbi]

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.

[tamerj1]

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-t...
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

[tamerj1]

@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 

[Abbi]

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 

[Icey · Accepted solution]

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.

[ValtteriN]

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/