Ambiguous paths in data model

[afbraga66]

Hello,

Currently on my data model, I am facing a situation in which I'm not seeing a decent way to handle it. I want to leverage language filtering in the model. For this I created a dimension (Dim_Language) which will filter other dimensions(Dim_DowntimeReason,Dim_DowntimeType), which in turn filter the fact table (F_DOWNTIME). Since I have the language the relationship between the dimensions and fact tables becomes a many-to-many relationship. To solve this I created bridge tables. So far so good. My issue now is that I want to filter the fact table with the dimensions, but if I enable the BOTH direction on the relationship betwen F_DOWNTIME/Dim_DowntimeReason 1->* Dim_DowntimeReason, I get the error below.

How would you approach this situation?

Thank you.

Best regards,

André

[amitchandak · Accepted solution]

@afbraga66 , Dim_language has two path to f_downtime.

In such case we do not join dim to both the dim and use it value to filter the measure

or join to one and use filter in measure to filter that

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

or

https://docs.microsoft.com/en-us/dax/treatas-function

[afbraga66]

Hey @amitchandak 

So I tried to add TREATAS() to my measure, but it is not working correctly when I filter the Language Code.

Downtime (min) =

CALCULATE(

    SUM(F_DOWNTIME[Duration Minutes 2]),

    USERELATIONSHIP(Dim_AREA[%Key1_(EMPRESA.COD_AREA)],F_DOWNTIME[%FKey1_(EMPRESA.COD_AREA)]),

    USERELATIONSHIP(Dim_Calendar[Manufacturing Date],F_DOWNTIME[Manufacturing Date]),

    USERELATIONSHIP(Dim_PLANT[Plant Code], F_DOWNTIME[Plant Code]),

    KEEPFILTERS(NOT F_DOWNTIME[Downtime Type Code] IN {"1","2","4","5","Z","Y"}),

    TREATAS(VALUES(Dim_Language[Language Code]),Dim_DowntimeType[Language]),

    TREATAS(VALUES(Dim_Language[Language Code]),Dim_DowntimeReason[Language])

)

It returns:

If I remove the last TREATAS() it works:

Can you help me understand?

Thank you,

André.

[afbraga66]

Hey @amitchandak 

Afterall it was some data issues. I have managed to find what was causing it.

Thanks for help.

Best regards,

André