Forum Discussion
Helper IHow to create a custom table or Matrix table
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.
Community ChampionHi 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
Community ChampionAbbi
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