Forum Discussion

jsh121988's avatar
jsh121988
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
Solved

Deactivate Relationship in a measure

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, inst...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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 )
)

     

jsh121988's avatar
jsh121988
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
OwenAuger
Icon for Super User rankSuper User
8 years ago

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 )
)

 

  • jsh121988's avatar
    jsh121988
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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))
  • nadavsnn's avatar
    nadavsnn
    Frequent Visitor
    4 years ago

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

  • Simeon's avatar
    Simeon
    Frequent Visitor
    2 years ago

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