Forum Discussion
Relating data from 3 tables
Hi All,
I have a scenario where i am doing a reconilliation between 2 data sets and need to populate comments from a 3rd data set incase results are not true. Below is an example where i have my master recon from 2 data sets and Notes column from 3rd data set.
Issue here is to pull notes for #6 and 7 which only exisit in one data set and i am not able to create any relationship between all 3 tables properly.
| Ticker -table 1 | Name - Table -1 | Ticker -table 2 | Name - Table -2 | Name Match | Notes |
| 1 | Jimmy | 1 | Jim | FALSE | Name changed |
| 2 | Rambo | 2 | Rambo | TRUE | |
| 3 | Maverik | 3 | Maverik | TRUE | |
| 4 | Rick | 4 | Rick | TRUE | |
| 5 | Eric | 5 | Eric | TRUE | |
| 6 | Jason | FALSE | ??? | ||
| 7 | Jacob | FALSE | ??? |
Here is the snip of 3rd data set as well-
3rd data set -
| Ticker - Table 3 | Notes |
| 1 | Name changed |
| 6 | Removed from group |
| 7 | Removed from group |
Any help would be appreciated.
Hi akathuri01
Based on your needs, I have created the following table.
You can use the following Measure to get the matching status of the two tables:MEASURE = IF ( ISBLANK ( SELECTEDVALUE ( Table1[Name - Table -1] ) ) || ISBLANK ( SELECTEDVALUE ( Table1[Ticker -table 1] ) ) || ISBLANK ( SELECTEDVALUE ( Table2[Name - Table -2] ) ) || ISBLANK ( SELECTEDVALUE ( Table2[Ticker -table 2] ) ), "Removed from group", IF ( SELECTEDVALUE ( Table1[Ticker -table 1] ) = SELECTEDVALUE ( Table2[Ticker -table 2] ) && SELECTEDVALUE ( Table1[Name - Table -1] ) = SELECTEDVALUE ( Table2[Name - Table -2] ), "Match", "Name changed" ) )
Create a new table, and then make the following settings in the filter:Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
I think I would do this all in Power Query.
Table2 seems to be the original table so a Merge with Table1 (left join on Ticker) . Call this tableX.
Then Merge TableX with Table3 (left join on Ticker)
--
This should get you the table in your post. You can do this in DAX with relationships but you might get in trouble because powerbi would insist on aggregations in one of the tables and give you error messages about relationships which can be confusing.
--
The above method assumes Table2 has data that can be removed. If Table1 has rows which are not in Table2 the joins will be different
Thanks much. the problem is, my universe can change in any of the tables and i am not sure if assuming table 2 as a base will do the trick. table 3 will always have comments for all cases i.e. - removal/additon/changes in any of the 2 tables. Its just i need to make this report smart enough to make my "Master Recon" read from notes for all exceptions.
If the Ticker is as simple as shown, you could standardise table1 and table2 by making a table of Ticker Numbers 1..x (where x is the largest ticker from table1 and table2 ) and using that and Left Join Merge on both tables.
You then do a Merge inner join of these 2 tables because they both have rows for all Ticker numbers.
Then do a Merge Inner join on table3--
Hi akathuri01
Based on your needs, I have created the following table.
You can use the following Measure to get the matching status of the two tables:MEASURE = IF ( ISBLANK ( SELECTEDVALUE ( Table1[Name - Table -1] ) ) || ISBLANK ( SELECTEDVALUE ( Table1[Ticker -table 1] ) ) || ISBLANK ( SELECTEDVALUE ( Table2[Name - Table -2] ) ) || ISBLANK ( SELECTEDVALUE ( Table2[Ticker -table 2] ) ), "Removed from group", IF ( SELECTEDVALUE ( Table1[Ticker -table 1] ) = SELECTEDVALUE ( Table2[Ticker -table 2] ) && SELECTEDVALUE ( Table1[Name - Table -1] ) = SELECTEDVALUE ( Table2[Name - Table -2] ), "Match", "Name changed" ) )
Create a new table, and then make the following settings in the filter:Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much. It worked for me.
