Create join table for many to many relationship DAX SUMMARIZE

[Niiru1]

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

[v-yiruan-msft · Accepted 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.

[Ashish_Mathur]

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.

[NamishB]

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

[smpa01]

@Niiru1 what is the end result you are hoping for?