Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jsh121988
Employee
Employee

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

 

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
OwenAuger
Super User
Super User

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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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 2Rolling 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

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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

Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.