Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gauri
Helper III
Helper III

issue with many to many relationship

Hi Team,

 

I am facing an issue with the slicer and the modelling part of the report.
I have 6 tables:
1) leaves_by_reason
2)d_DyanamicKPIClean
3)census_pcc_dates
4)query2
5)view_ods_payer_report_use
6)d_facility

I have created "one-to-many" relation between d_facility and rest of the other tables as displayed in the screenshot below.
When i try to create relationship between view_ods_payer_report_use and rest of the other tables except for d_facility, only many-to-many relation is avaiable
which is not useful as i would be using one column from view_ods_payer_report_use in the slicer and that slicer should affect the rest of the visuals in the report.
I have tried merging of two tables : d_facility and view_ods_payer_report_use but it throws an error saying there are some duplicates in my id column of d_facility table and this is not allowed in one side of "one-to-many" relation.Creating a distinct table or bridge table would not work beacuse there are no similar column with whom i can link.

Capture1.PNG

 

Thanks,
Gauri

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@gauri 

This not letting me know what is you, data model. You need to find the common dimension between tables.  It might be possible few tables are dimensions. You can create a new dimension or bridge tables.

Refer:

https://docs.microsoft.com/en-us/power-bi/guidance/

View solution in original post

6 REPLIES 6
gauri
Helper III
Helper III

Hello,

 

In my screen shot 1 i have column named as "coins_artyp" which has value IXA and there are two amounts attached with it so i am able to get only 1056 amount i am not able to get the other amount.

 

I have created a calculated calculated column in power bi Capture new 1.PNG

 

Capture new 2.PNG

 

to get the amount using this:

SUMX(summarize(FILTER(ar_tran_room,( ar_tran_room[coins_artyp] = ar_tran_rcpt[artyp] )&& ar_tran_room[cono] = ar_tran_rcpt[cono] && ar_tran_room[resno] = ar_tran_rcpt[resno] && ar_tran_room[gl_period] = ar_tran_rcpt[svc_date] ),ar_tran_room[cono], ar_tran_room[resno], ar_tran_room[gl_period] , "_max" ,max(ar_tran_room[coins_amt])),[_max]) 

 

The above dax is working good for some residents but its giving some kind of anomaly for some of the residents. I am not able to figure out why this is causing issue

amitchandak
Super User
Super User

@gauri 

This not letting me know what is you, data model. You need to find the common dimension between tables.  It might be possible few tables are dimensions. You can create a new dimension or bridge tables.

Refer:

https://docs.microsoft.com/en-us/power-bi/guidance/

Building bridge table willnot work in this scenario as there is no common column in other table

hi  @gauri 

For many to may relationship, please refer to this document:

https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships

 

and you need to create multiple dim tables for different fact tables.

 

Regards,

Lin

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

Hi @amitchandak can we do a skype call according to your availability today? so that we can get in depth of the issue and try to solve it?

hey @Anmolgan can we zoom in to solve my issue, so that i will be able to explain it to you in more depth.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors