issue with many to many relationship

[gauri]

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.

Thanks,
Gauri

[gauri]

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 

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 · Accepted solution]

@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/

[gauri]

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

[v-lili6-msft]

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

[Anmolgan]

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?

[gauri]

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