Relationships between 2 tables without unique column values

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

@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.

[PaulDBrown]

@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]
RETURN

COUNTROWS(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)

[nandic]

@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.

[Pragati11]

Hi @Anonymous ,

It will be nice to have some sample data based screenshots from both of your tables.

Thanks,

Pragati

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?