Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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.
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
@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
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
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.
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/
Proud to be a Super User!
User | Count |
---|---|
21 | |
20 | |
12 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
14 | |
13 | |
11 |