Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables:
Assignment Table
ASSIGNMENT_ID | EFFECTIVE_START_DATE | EFFECTIVE_END_DATE | PERSON_ID |
20127 | 12/08/2016 | 18/08/2016 | 19547 |
20127 | 22/09/2017 | 25/09/2017 | 19547 |
10153 | 04/03/2019 | 30/04/2019 | 15760 |
Employee Table:
PERSON_ID | DATE_START | DATE_END |
123 | 06/05/2014 | 26/06/2014 |
19547 | 11/03/2019 | 16/03/2019 |
19547 | 12/01/2018 | 17/01/2018 |
However when trying to create a relationship it says:
This relationship has cardinality Many-Many. This only be used if it is expected that neither column (PERSON_ID and PERSON_ID) contains unique values, and that the significantly different behaviour of Many-Many relationships is understood.
Is it possible to create a join table from a DAX query e.g. summarize table?t
Thanks
See file attached.
https://www.dropbox.com/s/2rv8is5q63g30td/PERSON_ID%20MANY%20TO%20MANY.pbix?dl=0
Solved! Go to Solution.
Hi @Niiru1 ,
You can create a calculated table as below:
Table =
VAR assignm_TreatAs =
TREATAS (
'Employee Table',
'Assignment Table'[PERSON_ID],
'Employee Table'[DATE_START],
'Employee Table'[DATE_END]
)
RETURN
NATURALLEFTOUTERJOIN ( assignm_TreatAs, 'Assignment Table' )
Or you can achieve it by Merge Queries in Power Query.
How to Change Joining Types in Power BI and Power Query
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Hi @Niiru1 ,
You can create a calculated table as below:
Table =
VAR assignm_TreatAs =
TREATAS (
'Employee Table',
'Assignment Table'[PERSON_ID],
'Employee Table'[DATE_START],
'Employee Table'[DATE_END]
)
RETURN
NATURALLEFTOUTERJOIN ( assignm_TreatAs, 'Assignment Table' )
Or you can achieve it by Merge Queries in Power Query.
How to Change Joining Types in Power BI and Power Query
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Hi,
From the Employee Table, create a Table with only 1 column of unique Person_ID's. This can easily be done in the Query Editor. Now link each of your source data tables to this single column unique employee ID table.
Hope this helps.
Hi @Niiru1
You might need a bridging table in between when doing Many to Many relationships in Power BI.
Can you have a look at-
https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystified
Hope this helps
Cheers,
-Namish B
@Niiru1 what is the end result you are hoping for?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |