Forum Discussion
Relationships between 2 tables without unique column values
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
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)