cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yvtoropov
Regular Visitor

Ambiguity in a direct active relationship between fact and snowflake dimensions

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.

 

Model.JPGWarning.JPG

1 ACCEPTED SOLUTION
BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

3 REPLIES 3
BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

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?

 

 

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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors