Forum Discussion

David_Verdon's avatar
David_Verdon
Regular Visitor
1 year ago
Solved

USE Relationship with Switch

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's avatar
    bhanu_gautam
    1 year ago

    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's avatar
    johnt75
    1 year ago

    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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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
  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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

  • David_Verdon's avatar
    David_Verdon
    Regular Visitor
    1 year ago

    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's avatar
      bhanu_gautam
      Super User
      1 year ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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