How does power bi resolve ambiguity when we use USERELATIONSHIP?

Question

Suppose I have a date table that filters product and product history tables. And then I try to add a link between product and product history (m:m on the name column in both tables), then Power BI doesn't allow me to add an active link (Or make a link active after adding) since there would be ambiguity introduced.

But if I add the inactive relationship and then create a mesure using DAX CALCULATE with USERELATIONSHIP between product and product history tables,

CALCULATE (
    SUMX (
        PRODUCT_CALLS,
        SUMX ( RELATEDTABLE ( PRODUCT_HISTORY ), PRODUCT_HISTORY[PRODUCT_USAGE] )
    ),
    USERELATIONSHIP ( PRODUCT_CALLS[NAME], PRODUCT_HISTORY[NAME] )
)

Does this result in an AND filter of the product usage table (that is - filter from date AND product tables). Or does power bi randomly choose whether the filter is from date table or the product table?
In the above formula if I don't use CALCULATE, how would RELATEDTABLE get evaluated? As there is no link, how would it get evaluated?

sergiom sergiom · Accepted Answer · 2021-02-05T16:26:43

since you use USERELATIONSHIP, you force DAX to solve the ambiguity by using that relationship instead of the active relationship existing between the date and the history table. This because the ambiguity in this simple case is that there are two paths from the date table to reach the history table and only one is to be chosen.
the RELATEDTABLE is needed to follow the one to many relationship starting from the row context over PRODUCT_CALLS created by the SUMX iterator. The CALCULATE in this formula is only used to apply the USERELATIONSHIP

Edit: to test point 1 I created a small sample model with three tables

Date = CALENDAR( "2020-01-01", "2020-01-31" )

Product = 
DATATABLE( 
    "Name", STRING, "Date", DATETIME, "Quantity", INTEGER,
    {
        { "A", "2020-01-01", 1 },
        { "B", "2020-01-01", 2 }
    }
)

ProductHistory = 
DATATABLE(
    "Name", STRING, "Date", DATETIME, "Quantity", INTEGER,
    {
        { "A", "2020-01-02", 10 },
        { "A", "2020-01-03", 20 },
        { "B", "2020-01-01", 30 }
    }
)

Then I ran this query in DAX studio to see how the relationships are used.

EVALUATE
GENERATE(
    ALL( 'Date'[Date] ),
    CALCULATETABLE(
        ProductHistory,
        USERELATIONSHIP ( 'Product'[Name], ProductHistory[Name] )
    )
)

The result shows that the 'Date'[Date] ProductHistory[Date] relationship is ignored

Edit 2: I tested the measure

My measure = CALCULATE ( SUM ( PRODUCTHISTORY[QUANTITY] ) , USERELATIONSHIP (PRODUCTHISTORY[NAME], PRODUCT[NAME] )

The result shows that Date filters Product that Filters ProcutHistory

Using ALL('Date') creating the measure

My measure All =
CALCULATE (
    SUM ( PRODUCTHISTORY[QUANTITY] ),
    USERELATIONSHIP ( PRODUCTHISTORY[NAME], PRODUCT[NAME] ),
    ALL ( 'Date' )
)

removes the filter from Date but does not affect the relationship between ProductHistory[Name] and Produt[Name]

How does power bi resolve ambiguity when we use USERELATIONSHIP?

1 Answers