Understanding Ambiguity in Power Data Model
Have you come across the following message while establishing a relationship between two tables and wondered what it means?
If yes, then read along.
This article is my effort to decode this in a simple possible manner.
Ambiguity means interpreting in more than one way. Or, in simple words, what is the appropriate choice? There is uncertainty.
In a data model, we use the relationship to propagate filters between two tables. Like any robust system, the Power BI data model requires a clearly defined path for the filter propagation.
Let us understand with examples.
Scenario 1
The simplest example of an ambiguity in a data model is establishing more than one relationship between the two tables.
There can be one active relationship between two tables (denoted by a solid line). All the other relationships are always inactive (indicated by a dotted line)
In the above example, Orders[Order Date] and Orders[Ship Date] is related to ftCalendar[Date]. The relationship between Orders[Order Date] and ftCalendar[Date] is active, whereas the other is inactive.
I have added the following measure to the model:
Order Qty = SUM(Orders[Order Quantity])When we use the above measure in a visual and ftCalendar[Year], it returns total orders by Order Date, grouped by year.
Power BI is propagating the filter through the active relationship between the two tables (Date to Order Date).
Imagine if both the relationships between the Order table and Calendar table is active. Which Order Quantity would the above visual be returning? Ambiguous.
Power BI simply doesn’t allow us to create any ambiguous scenarios for the data model to avoid the above scenario.
Scenario 2
Consider the following scenario:
Manager[City] is related to Target[City] (Status — Active)
Manager[City] is related to Customer[City] (Status — Active)
When we establish a relationship between Customer[Customer Segment] and Target[Customer Segment], Power BI does not allow it to be active.
It says an active relationship between Target and Customer table creates an ambiguous relationship.
For a moment, let’s imagine that the relationship between Customer and Target is active. In that scenario, there will be two paths available for the model to choose for the filter propagation when we apply a filter from the Manager’s table:
Power BI model avoids such ambiguous decisions for the filter propagation.
What is the alternative? We can temporarily active an inactive relationship with the help of USERELATIONSHIP. USERELATIONSHIP temporarily activates the inactive relationship (defined in the formula) and deactivates others to remove ambiguity. For more details, please see:
I hope it is making some sense now.
Or, is it still ambiguous?
I write about MS Excel, Power Query, Power BI, Power Pivot, DAX, Data Analytics, and sometimes travelling.
