cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Niiru1
Helper IV
Helper IV

Create join table for many to many relationship DAX SUMMARIZE

I have two tables:
Assignment Table

ASSIGNMENT_IDEFFECTIVE_START_DATEEFFECTIVE_END_DATEPERSON_ID
2012712/08/201618/08/201619547
2012722/09/201725/09/201719547
1015304/03/201930/04/201915760

 

Employee Table:

PERSON_IDDATE_STARTDATE_END
12306/05/201426/06/2014
1954711/03/201916/03/2019
1954712/01/201817/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

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

Create join table for many to many relationship DAX SUMMARIZE.JPG

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.

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
yingyinr
Community Support
Community Support

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

Create join table for many to many relationship DAX SUMMARIZE.JPG

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.

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
NamishB
Post Prodigy
Post Prodigy

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
Resident Rockstar
Resident Rockstar

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


New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors