cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsh121988
Microsoft
Microsoft

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!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

5 REPLIES 5
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!

My Blog
Connect on Twitter
Connect on 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!

My Blog
Connect on Twitter
Connect on LinkedIn
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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors