Need help: Ambiguity between tables in singe direction model

[AXinter]

Hi all,

I've been exploring the possiblility of shifting our standard star schema model to more of a "starflake" model, where we use our dimCompany table as an outrigger table to filter our other dimension tables (such as dimPart or dimCustomer) since Company is a common field in all of our dimension tables. Thus, by filtering on a specific company using dimCompany in our PBI report, the rows in dimPart and dimCustomer would filter to the chosen company and in turn filter out our Sales Order fact table. 

However, with this setup, it doesn't seem possible to have two active relationships between the "sub"-dim tables (dimPart and dimCustomer) and the fctSalesOrder (see an example below):

Question 1)
Why is this an ambigious relationship when I have singe directional relationship in the model?

Question 2) 
Could you provide an example or practical case illustrating how this might cause an issue in this model?

Question 3)
Am I required to use DAX to create a relationship between fctSalesOrder and DimPart (and all my other dimension tables)? What can I do?


I've looked at other examples here in the community and watched some YouTube videos about ambiguity but can't find a good solution to the problem.

Any help would be much appreciated!

[v-rzhou-msft · Accepted solution]

Hi @AXinter ,

Question 1) and Question 2) should be one question.

As far as I know, Power BI does't support us to create two active relationships between two tabels.

According to Power BI warning, we know that if we create a relationship between the 'DimPart' and the 'fctSalesOrder', Power BI will get confused on the filter between 'DimCompany' and 'fctSalesOrder'.

From above screenshot, we can find that if the relationship between 'DimPart' and 'fctSalesOrder' is active, 'DimCompany' could filter 'fctSalesOrder' by two ways.

'DimCompany'>'dimCustomer'>'fctSalesOrder'

'DimCompany'>'DimPart''>'fctSalesOrder'

So you couldn't create active relationships between 'DimPart'/'DimCustomer' with 'fctSalesOrder' at the same time.

Question 3)

I suggest you to try USERELATIONSHIP() function to use inactive relationship.

Measure = 
calculate(sum('fctSalesOrder'[value]),userelationship('DimPart'[column],'fctSalesOrder'[column]))

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@AXinter , You have two paths to reach from the company to fact order. That is causing an issue.

Ideally company should join with Fact order.

In case you want to filter the slicer customer and item you create measure and use that as visual level filter

M1= countrows(filter(customer, Customer[Company] in values(company[Company]) ))

M2= countrows(filter(item, item[Company] in values(company[Company]) ))

Use the above measures in the respective slicers and check for not blank