Forum Discussion
Ambiguity between tables in data model
Say I have the following data model:
The idea is to build three slicers at report level - Organization, Customer and Department.
When filtering data by Organization, the Customer and Department slicers should show the results regarding the Organization values that are selected on its slicer. That should then filter the fact tables results by intersecting the results of the Customer and Department values.
It seems that Power BI does not allow for the activation of the relationship between DIM_ORGANIZATION and DIM_DEPARTMENT because that would introduce ambiguity:
Is there any way to achieve what I need by redesigning my model? Or is the model valid and I should focus on reaching a solution based on DAX or report level functionalities?
Anonymous , If you are looking to filter those in slicer. Create a measure in fact
countrows( Fact_sales) + countrows( Fact_order)
And use that in the visual level filter of these slicers and check for non-blank values
you can use that in other visuals too, as per need
refer
3 Replies
- amitchandak
Super User
Anonymous , You are having two path to reach a Fact like sales from Organization. Power bi need only one path.
So either merge ognizations with both tables. Or, if there is a column in fact where it join directly then do not join with dimension. Join with fact
amitchandak
When you write "join directly" do you mean something like this?Having this design would not filter Customer and Department values when using the Organization slicer.
- amitchandak
Anonymous , If you are looking to filter those in slicer. Create a measure in fact
countrows( Fact_sales) + countrows( Fact_order)
And use that in the visual level filter of these slicers and check for non-blank values
you can use that in other visuals too, as per need
refer