topic Re: USE Relationship with Switch in DAX Commands and Tips

USE Relationship with Switch

David_Verdon — Thu, 05 Dec 2024 10:00:59 GMT

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?

Re: USE Relationship with Switch

bhanu_gautam — Thu, 05 Dec 2024 10:39:30 GMT

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

Re: USE Relationship with Switch

David_Verdon — Thu, 05 Dec 2024 11:22:31 GMT

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

Re: USE Relationship with Switch

bhanu_gautam — Thu, 05 Dec 2024 12:04:52 GMT

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.

Re: USE Relationship with Switch

Anonymous — Fri, 06 Dec 2024 06:15:23 GMT

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

Re: USE Relationship with Switch

johnt75 — Fri, 06 Dec 2024 15:57:24 GMT

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