Ambiguity in a direct active relationship between fact and snowflake dimensions

[yvtoropov]

Good day!

I have a fact table FactSessions which is linked to two dimensions DimDepartment and DimClient. Both Dimensions have a shared location snowflaked dimension DimGeography like in the screenshot below to specify part of address like Suburb/State/Postocode.  However, PowerBI disables one of the relationships to one of main dimensions (in this case DimClient) due to possible ambiguity for DimGeography table per warning below (taken as I tried to activate that relationship). I have searched the forums and found couple of issues related to joining same dimension to multiple fact tables which suggests changing cross filter direction to 'Single' from 'Both'. But my scenario is slightly different and all cross filter directions in this case are 'Single' . I understand how powerbi may be confused with respect to relationship to DimGeography, but from use prospective DimGeography is not standalone an relates to DimClient and DimDepartment, meaning it will be either clients or departmen's state/suburb/postcode. How should this kind of ambiguity be resolved? Should I flatten out each dimension DimClient and DimDepartment to include its own postcode/state/suburb? It looks like surely will work, but is there a way to achieve it with current design?

Thanks,

-Yuriy

P.S. I tried to change cross filter direction to 'Both' between DimGeography and mother dimensions DimClient and DimGeography to look each snowflake dimension like one flattened but ambiguity would not go away.

[BhaveshPatel · Accepted solution]

DAX allows you to override the active relationship and use the inactive relationship in your formulas instead.  To do this, you must use the DAX function USERELATIONSHIP inside a CALCULATE function.

The relationship MUST exist in the data model and be set as inactive for the above to work.  You can’t simply use the function USERELATIONSHIP and specify two columns and expect it to work – you must have the inactive relationship set up first.

[yvtoropov]

Thanks for pointers!

So, I should go ahead and create a new measure (or a new calculated column ?? ) using calculation like this if I want distinct clients 

CountClients = CALCULATE(DISTINCTCOUNT(FactSession[ClientKey]), USERELATIONSHIP(DimClient[ClientKey],FactSession[ClientKey]))

and then, say, plot a visual of this new measure vs DimGeography post code if I want count of clients per client postcode, correct?

[BhaveshPatel]

Yes. You are right. Keep the relationship between DimClient and DimGeography Active.