Forum Discussion
Relationships between 2 tables without unique column values
Hi
Got 2 tables: Receipt_Headers and Receipt_Payment.
These 2 tables have 3 columns in common: RCP_Date, Receipt_ID and Amount. I get most of the data I need from Receipt_Headers but I have to add 1 column from Receipt_Payment ( Payment_ID ).
None of these columns are unique and I need to make a working relationship between those 2 tables somehow.
When I make an active relationship between Receipt_IDs only I get incorrect data. Visual shows 2 rows ( same receipt id, same date, same amount, different Payment_ID ) but in SSMS in Receipt_Payment I can see ( same receipt id, same date, different amount, different Payment_ID ).
In Receipt_Headers I have only 1 value for that Receipt_ID but I need to add Payment_ID to that visual but it takes both values from Receipt_Headers.
So I tried to make another relationship between those 2 tables now using Amount column but I can't make 2 active relationships between them so I made an inactive one and tried to use USERELATIONSHIP:
Payment ID = CALCULATE(SUM(Receipt_Payments[PAYMENT_ID]), USERELATIONSHIP('Receipt_Headers'[TOTAL_AMOUNT_INC],Receipt_Payments[AMOUNT])
Now for starters I dont want to use SUM ( I don't want to sum or average or anything to that Payment_ID column since its a text column. ) Tried using instead of sum "all" but that didn't work and when I try to add that new measure to my visual it breaks my entire visual.
Im guessing if I get this measure to work somehow then the data should be correct, it could compare receipt_IDs and amounts and show only data where both these columns are equal.
Anonymous - I'm with nandic on this one, a composite key seems like the way to go, I like to create my composite keys like this:
Key = [Column1] & "|" & [Column 2] & "|" & [Column3]I always use a seperator because I have seen the rare instance where just squashing columns together ends up creating duplicate keys. Think 11 in first column and 1 in second column and 1 in first column, 11 in second column.
I also agree with everyone else that sample data would be tremendously beneficial.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
5 Replies
Hi Anonymous ,
It will be nice to have some sample data based screenshots from both of your tables.
Thanks,
Pragati
Anonymous ,
Did you try to use composite key to make relationship between these tables? Will composite key (example: RCP_Date + Receipt_ID ) return unique value in at least on table?
You should avoid creating relationships using amount columns.Anonymous - I'm with nandic on this one, a composite key seems like the way to go, I like to create my composite keys like this:
Key = [Column1] & "|" & [Column 2] & "|" & [Column3]I always use a seperator because I have seen the rare instance where just squashing columns together ends up creating duplicate keys. Think 11 in first column and 1 in second column and 1 in first column, 11 in second column.
I also agree with everyone else that sample data would be tremendously beneficial.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Anonymous , Can you share sample data and sample output in table format?
Anonymous
A couple of options you can use WITHOUT creating a relationship of many-to-many between tables (ie the tables are not related at all):
1) Use TREATAS In the filter expression:
example CALCULATE(SUM(Table 1[values]), TREATAS(VALUES(Table 1[ID]), Table 2[ID))
where [ID] is the common field
2) Use CALCULATETABLE to filter the values you need:
example
VAR table1 = VALUES(Table 1[ID])
VAR table2 = VALUES(Table2 [ID]
RETURNCOUNTROWS(INTERSECT(table1,table2)
(or use EXCEPT instead of INTERSECT if tou want the values in Table 1 which are not in Table 2 in the filter context)
