Forum Discussion
Show all rows
Hi all,
I am trying to combine 2 tables into 1 from 2 different sources.
They both share the relationship ID.
Table 1 contains 5 unique IDs while table 2 only contains 3 of the 5 ID and employee's pay. (simplified version of my problem)
When I made the relationship and try to use the table visualisation, only the 3 IDs is being displayed, is there any way I can show all 5 IDs in the table and rows with no data will show the data as blank?
Table 1:
1 |
| 2 |
| 3 |
| 4 |
| 5 |
Table 2:
| 2 | $100 |
| 3 | $85 |
| 5 | $55 |
I would like it to show:
| 1 | |
| 2 | $100 |
| 3 | $85 |
| 4 | |
| 5 | $55 |
Thank you for your help 🙂
8 Replies
Hi Anonymous ,
What kind of relationship have you got? Is it one-directional? Please paste your data model here.
It should work, if you move ID column from Table1 and PAY column from Table2 (without setting any summarisation as Power BI by-default sets a summarisation of SUM or AVERAGE on numeric columns)
Thanks,
Pragati
Hi Pragati11
Let me correct muself. Just checked my data again. (I simplified it for easier understanding) It is more of
Table 1:
1
2 3 4 5 3 3
Table 2:
2 $100
3 $85 5 $55 9 $75 The 2 columns that I am trying to form a relationship with is the 2 IDs and I am expecting this output:
1 2 $100
3 $85 3 $85 3 $85 4 5 $55 The current relationship they have now is a many to many relationship for ID as no other relationship seems to work. The expected data may not make sense but there are other columns that exist as well. I just wanted to simplify it.
Many thanks in advanced.
Hi Anonymous ,
Please turn on the option of "Show items with no data".
Anonymous To make this work as expected you will need to create a DimEmployee table that has each Employee ID only once. See if this post helps make it more clear: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html
