Why do I always have to add CROSSFILTER + NONE when I use USERELATIONSHIP ?

[molegris]

Hi,

When I read documentation, blogs and forums, it seems like USERALATIONSHIP can be used alone.  It seems so simple yet I cannot get it to work without having to add a CROSSFILTER to disable the default active relationship.

Here's my usecase :

Basically I get it to work in the T3 test by explicitly disabling the default relationship using CROSSFILTER + NONE. 

But I don't understand why the T2 option does not work.

Default active relationship is Programme[Code] = RelanceProgramme[Programme]

The inactive relationship is Programme[DecReference] = RelanceProgramme[Programme]

Here's the row from the Programme dimension.

Record from the dimension table Programme

DEFINE
    // BASELINE using the default active relationship
    VAR _t1 =
        CALCULATETABLE ( RelanceProgramme, Programme[Code] = "NWE.0F" )
        
    // Attempt #1 to activate the second relationship  (FAIL)
    VAR _t2 =
        CALCULATETABLE (
            RelanceProgramme,
            USERELATIONSHIP ( Programme[DecReference], RelanceProgramme[Programme] ),
            Programme[Code] = "NWE.0F"
        )
        
    // Attempt #2 to acticate the second relationship (SUCCESS)
    VAR _t3 =
        CALCULATETABLE (
            RelanceProgramme,
            USERELATIONSHIP ( Programme[DecReference], RelanceProgramme[Programme] ),
            CROSSFILTER ( Programme[Code], RelanceProgramme[Programme], NONE ),
            Programme[Code] = "NWE.0F"
        )

T1 - Using the default active relationship (BASELINE)

T2 - Trying to activate the second relationship with USERELATIONSHIP. But I get same results as T1. Why is this not working ??

T3 - Activating the second relationship AND explicitly disabling the default one using CROSSFILTER + NONE. (That works fine!)

 Thanks

--mo

[AlexisOlson · Accepted solution]

I'd love for someone like @marcorusso to give a more definitive answer but here's my guess for what's happening.

Typically, when you have multiple relationships like this, it's usually one column from a dimension table relating to two separate columns in a fact table, for example, dimDate[Date] 1-->* fact[Ship Date] and dimDate[Date] 1-->* fact[Delivery Date]). In your case, you have two different columns on a dimension table filtering the same column on the many-side of the relationship. USERELATIONSHIP will override an active relationship from the same column on the 1-side but it doesn't appear to deactivate additional relationships to the same column on the many-side, just like it would not deactivate relationships from other tables to that column.

[molegris]

Thank you for pointing out this subtile but important distinction.  Make sense! 🙂