USE Relationship with Switch

[David_Verdon]

I'm working on a DAX measure to calculate Sales Value based on project status. The measure should use different date relationships for "Closed Project" and "Live Project" statuses. Here's the measure I'm using:

Sales Value =
VAR Status_ = SELECTEDVALUE(ProjectDetails[ProjectStatus])
VAR Rev = [Revenue Actual]
VAR Fee = SUM(ProjectDetails[FeeEstimate])
VAR Result =
SWITCH(
TRUE(),
Status_ = "Closed Project",
CALCULATE(Rev, USERELATIONSHIP(ProjectDetailsClosedDate], 'Date'[Date])),
Status_ = "Live Project",
CALCULATE(Fee - Rev, USERELATIONSHIP(CMap_ProjectDetails_Live[WonDate], 'Date'[Date])),
BLANK()
)
RETURN
Result

The goal is to:

Use the ClosedDate relationship for "Closed Project" status.
Use the WonDate relationship for "Live Project" status.
However, when I add a slicer from the Date table, only "Closed Project" data is shown. I suspect the active relationship with ClosedDate is overriding the measure for "Live Project."

I've tried using REMOVEFILTERS('Date') to clear existing filters before applying USERELATIONSHIP, but it hasn't resolved the issue. Any suggestions on how to ensure the slicer works correctly for both statuses?

[bhanu_gautam · Accepted solution]

@David_Verdon , Try using this then

DAX
Sales Value =
VAR Status_ = SELECTEDVALUE(ProjectDetails[ProjectStatus])
VAR Rev = [Revenue Actual]
VAR Fee = SUM(ProjectDetails[FeeEstimate])
VAR Result =
SWITCH(
TRUE(),
Status_ = "Closed Project",
CALCULATE(
Rev,
USERELATIONSHIP(ProjectDetails[ClosedDate], 'Date'[Date])
),
Status_ = "Live Project",
CALCULATE(
Fee - Rev,
USERELATIONSHIP(CMap_ProjectDetails_Live[WonDate], 'Date'[Date]),
REMOVEFILTERS('Date'),
USERELATIONSHIP(ProjectDetails[ClosedDate], 'Date'[Date])
),
BLANK()
)
RETURN
Result

The USERELATIONSHIP function for ClosedDate is explicitly deactivated for the "Live Project" calculation.

[johnt75 · Accepted solution]

The problem is that variables in DAX aren't really variable, they are constants. Once they are evaluated they are never evaluated again, so trying to reevaluate them in a different context won't work. Try

Sales Value =
VAR Status_ =
    SELECTEDVALUE ( ProjectDetails[ProjectStatus] )
VAR Result =
    SWITCH (
        TRUE (),
        Status_ = "Closed Project",
            CALCULATE (
                [Revenue Actual],
                USERELATIONSHIP ( ProjectDetails[ClosedDate], 'Date'[Date] )
            ),
        Status_ = "Live Project",
            CALCULATE (
                SUM ( ProjectDetails[FeeEstimate] ) - [Revenue Actual],
                USERELATIONSHIP ( CMap_ProjectDetails_Live[WonDate], 'Date'[Date] )
            ),
        BLANK ()
    )
RETURN
    Result

[v-xuxinyi-msft]

Hi @David_Verdon 

Did bhanu_gautam 's method solve your problem? If so, could you please accept it as a solution? This will help more users who are facing the same or similar difficulties. Thank you!

If your problem persists, could you please provide example data and expected results based on the example data so we can better help you? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

[David_Verdon]

Hi @bhanu_gautam thank you

i tried using the Dax code as suggested but i get the same result 

Measure shows values for both project types as expected, however as soon as i introduce a selection from the date table/slicer it only displays closed projects - there is an active realtionship between closed date and Date and an inactive relationship between won date and date 

Thnaks

David 

[bhanu_gautam · Accepted solution]

@David_Verdon , Try using this then

DAX
Sales Value =
VAR Status_ = SELECTEDVALUE(ProjectDetails[ProjectStatus])
VAR Rev = [Revenue Actual]
VAR Fee = SUM(ProjectDetails[FeeEstimate])
VAR Result =
SWITCH(
TRUE(),
Status_ = "Closed Project",
CALCULATE(
Rev,
USERELATIONSHIP(ProjectDetails[ClosedDate], 'Date'[Date])
),
Status_ = "Live Project",
CALCULATE(
Fee - Rev,
USERELATIONSHIP(CMap_ProjectDetails_Live[WonDate], 'Date'[Date]),
REMOVEFILTERS('Date'),
USERELATIONSHIP(ProjectDetails[ClosedDate], 'Date'[Date])
),
BLANK()
)
RETURN
Result

The USERELATIONSHIP function for ClosedDate is explicitly deactivated for the "Live Project" calculation.

[bhanu_gautam]

@David_Verdon , Try using below DAX

Sales Value =
VAR Status_ = SELECTEDVALUE(ProjectDetails[ProjectStatus])
VAR Rev = [Revenue Actual]
VAR Fee = SUM(ProjectDetails[FeeEstimate])
VAR Result =
SWITCH(
TRUE(),
Status_ = "Closed Project",
CALCULATE(
Rev,
TREATAS(VALUES('Date'[Date]), ProjectDetails[ClosedDate])
),
Status_ = "Live Project",
CALCULATE(
Fee - Rev,
TREATAS(VALUES('Date'[Date]), CMap_ProjectDetails_Live[WonDate])
),
BLANK()
)
RETURN
Result