USERELATIONSHIP doesn't deactivate the active relationship

[juan_pablo]

Hi,

When I use the DAX function USERELATIONSHIPS it doesn't acts as the documentation says and it keeps using the active relationship instead of the indicated by the function.

I made this simple model to replicate the issue:

I want to count rows in the "Documents" table by Parcial using the inactive many to many relationship which uses shipmentOid as a key.

The DAX measure I'm using is:

Docs shipment = 
CALCULATE(
    COUNTROWS(Documents),
    USERELATIONSHIP('13'[shipmentOid],Documents[shipmentOid])
)

But the result is the one obtained by using the active relationship between tables "Parciales" and "Documents" (which is different from that using the relation between the inactive relationship).

I made the same measure without USERELATIONSHIP to compare:

Docs parcial = COUNTROWS(Documents)

and the results are identical:

If I manually deactivate the relationship between "Parciales" and "Documents" tables the function using USERELATIONSHIP works fine.
How can I deactivete the active relationship and ONLY USE the one indicated by USERELATIONSHIP?

This is the file if you want to check it: https://uniontecolog-my.sharepoint.com/:u:/g/personal/jreyesv_ecolog_com_co/EeQXlksluedAqoQos-K4snMB...

[v-henryk-mstf]

Hi @juan_pablo ,

Whether the advice given by @lbendlin  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[lbendlin · Accepted solution]

Time to update the documentation, I guess.  I see that it can be misinterpreted.

What you _can_  (probably) do is make the connection between Parciales and Documents inactive as well by default. That would then allow you to switch on either of them (but not both)

Or you could consider merging the tables 13 and Parciales.

[lbendlin]

That's not how this works. USERELATIONSHIP cannot be abused to try and trick the data model into accepting ambiguous connections between tables (multi path).  USERELATIONSHIP is used in cases where you have multiple connections between two tables (one active, and the rest inactive).  For example when you want to report on Order placement data, Order Ship data, and Order Delivery date  out of the same Order fact table.

Reconsider your data model.

[juan_pablo]

Hi @lbendlin , the documentation says:
"[...] it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments."

The model includes all tables, all relationships, I can't see anything in the documentation (nor DAX Guide from SQLBI) restricting to relationships between only two tables. Also, if the function "overrides any other active relationship" so there would not be ambiguous paths, so it won't be tricking the model.