Ambiguity between 2 facts; working except for 1 dimension

[hans_peeters]

Dear all, unfortunately i have an error with Ambiguity. The thing I thougt what was causing this error is a circular dependency issue. But this does not seem to be the case: I have 2 fact tables: ProductError and ActualError. I can connect alle dimension (with cross filter single) to both facts without any issues. But when I connect DimError I get the issue.

Only connecting DimError to both Facts without any other dimensions does work. But after that I cannot connect any other dimensions. I am guessing there is an issue with DimError, but cant figure out what. Do someone have an idea?

[Brian_M]

Is the problem being caused by the bi-directional cross filter direction of the relationship between DimSerialNumber and FactActualError? Can you try to make it single cross filter direction?

Otherwise, you manipulate the various measures to get the desired behaviour - e.g. if you need to end up with an Inactive relationship then you can use USERELATIONSHIP to activate it within specific measures to get the behaviour you want, or use CROSSFILTER to change filter direction in certain contexts.

You can also design measures that work across tables were there is no relationship by using LOOKUPVALUE

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

or try TREATAS

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Hope that helps! 

[hans_peeters]

Brian, unfortunately removing the cross direction both on serial number doesn't fix the issue. I was doing some testing and forgot reset is to single. My main concern is that every connection is working except the DimError. Can there be an other obvious thing I am completely missing?

[Brian_M]

Can you explain the reason why multiple ErrorKeys in the Dimension could pertain to one row in the Fact ProductActualError? It seems strange to me that you would have a Dimension on the 'many' side and the fact on the 'one' side.

I can see why the model would not allow you create the relationship as active because otherwise there would be two ways to get from, say, Dim Mri to FactProductError. A filter on Dim Mri would give an ambiguous (unresolvable) filter on FactProductError.

The two ways the filter would propagate are:

  1) Either directly via Dim Mri - FactProduct Error relationship, or also via

  2) Dim Mri - FactProductActualError - DimError-FactProductError 

The same would be true for all the other dimensions linked to both tables, i.e. Dim SerialNumber, Dim SystemType, Dim Configuration etc.

So the UI prevents you from creating the relationship.

If DimError was on the one side, and FactProductActualError was on the many, then the ambiguity would not occur.

If you can verify that DimError is supposed to be on the many side, then perhaps we can look at the measures you are trying to calculate for the DimProductActualError fact table and we can ensure those are written using USERELATIONSHIP to ensure the inactive relationship is used for specific measures. (e.g. CALCULATE ( COUNTROWS ( FactProductActualError ), USERELATIONSHIP ( DimError[Error_Key], FactProductActualError[Error_Key] )

Hope that helps!

[v-yulgu-msft]

Hi @hans_peeters,

Please check whether your scenarion is similar to this.

Regards,

Yuliana Gu