Deactivate Relationship in a measure

[jsh121988]

Hello,

I would like to perform the opposite of USERELATIONSHIP, in that I want to turn off a relationship for a particular measure. If possible, I would rather leave the active relationship, instead of applying USERELATIONSHIP to every measure.

I've looked into ALL and CROSSFILTER(None), but neither worked.

Right now, there is an active relationship between AH[NextChangedDate] and _dtDateFilter[Date filter].

_dmRollingAboveHours = 
CALCULATE(
    SUMX(
        ADDCOLUMNS(
            AH,
            "hours", 
                DATEDIFF(
                    MAX([ChangedDate] + (HourLimit[HourLimit Value] / 24), MIN(_dtDateFilter[Date filter])),
                    MIN([NextChangedDate], MIN(_dtDateFilter[Date filter]) + 1),
                    SECOND
                ) / 60 / 60
        ),
        [hours]
    ),
    FILTER(AH,
        AH[ChangedDate] + (HourLimit[HourLimit Value] / 24) < MIN(_dtDateFilter[Date filter]) + 1
        && AH[NextChangedDate] >= MIN(_dtDateFilter[Date filter])
        && AH[Duration_h] >= HourLimit[HourLimit Value]
    )

Thanks,

Jon

[OwenAuger]

Hi @jsh121988

There should be a solution to this - just want to clarify what you're doing.

What effect are you trying to achieve by turning off the relationship? Could you post some sample data & expected outputs.

ALL(_dtDateFilter) & CROSSFILLTER(AH[NextChangedDate], _dtDateFilter[Date filter] ,None) do different things.

• ALL would clear all filters on the table specified (I'm assuming _dtDateFilter) but maintain the relationship between the two tables, so any calculations that depend on the relationship would behave as expected, but with all filters on the table cleared.
• CROSSFILTER would remove the relationship but leave any filters intact. So any expressions involving the _dtDateFilter table would take into account filters on that table, such as MIN(_dtDateFilter[Date filter])

Regards,

Owen

[jsh121988]

Hi Owen,

As you can see from this section, I'm basically defining the relationship from AH to _dtDateFilter. I'm selecting rows where _dtDateFilter is between ChangedDate and NextChangedDate. With the relationship, it is effectively selecting rows where _dtDateFilter == NextChangedDate.

FILTER(AH,
        AH[ChangedDate] < MIN(_dtDateFilter[Date filter]) + 1
        && AH[NextChangedDate] >= MIN(_dtDateFilter[Date filter])

Having an active relationship adds an extra filter on the above which makes it look like this:

FILTER(AH,
        AH[ChangedDate] < MIN(_dtDateFilter[Date filter]) + 1
        && AH[NextChangedDate] >= MIN(_dtDateFilter[Date filter])
        && AH[NextChangedDate] == MIN(_dtDateFilter[Date filter])

 Rolling Hours Fig 1 and Fig 2

Fig 1 has the relationship enabled, and hours are summed when NextChangedDate == x-axis Date.

Fig 2 has the relationship disabled, and hours are summed if the Date was between ChangedDate and NextChangedDate. This effectively shows a rolling volume of hours over each day.

Thanks,

Jon

[OwenAuger · Accepted solution]

Thanks for the info 🙂
To ensure the measure is evaluated exactly as if the relationship doesn't exist, you should wrap your existing code in an outer CALCULATE with CROSSFILTER, something like below.

This ensures that all filter arguments in your existing CALCULATE are evaluated with the relationship disabled, and in particular the FILTER( AH, ... ) code is not subject to the physical relationship.

Does this give the expected result?

Regards,

Owen

_dmRollingAboveHours =
CALCULATE (
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                AH,
                "hours", DATEDIFF (
                    MAX (
                        [ChangedDate]
                            + ( HourLimit[HourLimit Value] / 24 ),
                        MIN ( _dtDateFilter[Date filter] )
                    ),
                    MIN ( [NextChangedDate], MIN ( _dtDateFilter[Date filter] ) + 1 ),
                    SECOND
                )
                    / 60
                    / 60
            ),
            [hours]
        ),
        FILTER (
            AH,
            AH[ChangedDate]
                + ( HourLimit[HourLimit Value] / 24 )
                < MIN ( _dtDateFilter[Date filter] ) + 1
                && AH[NextChangedDate] >= MIN ( _dtDateFilter[Date filter] )
                && AH[Duration_h] >= HourLimit[HourLimit Value]
        )
    ),
    CROSSFILTER ( AH[NextChangedDate], _dtDateFilter[Date filter], NONE )
)

[Simeon]

I've been tearing my hair out as to why it wasn't disabling the relationship... Thanks so much!!!

[nadavsnn]

@OwenAuger after 4 long hours of try and error your replay solved the issue. thanks!

[Anonymous]

Hi Owen

I'm tring to do the same thing here but the crossfilter & none did nothing for me 😞 , it returns the same as the relationship enabled which is differnet from the disabled result I wanted. Could you please help? 

#Open Claims = 
var __EoM = ENDOFMONTH(DateDim[Date])
return
CALCULATE(
    COUNT(DataTable[ID])
    
     ,FILTER(DataTable
        ,DataTable[Open Date]<=__EoM
        &&
        or(DataTable[Date Closed]>__EoM,ISBLANK(DataTable[Date Closed]))
       )
        
,CROSSFILTER(DataTable[Open Date],DateDim[Date],None))

[jsh121988]

Owen it worked perfectly!

It returned the exact Fig 2 chart from above.

Thank you so much. This will be a very useful tool in the future.

Thanks,

Jon