Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dax If Else

So i have a list of values i am looking for with completion dates i am comparing to.  i want to find if "Promotions" shows up and if it was completed on time, else if "Promotions' does not show up, look for "Closed" and if it completed on time.  

Completed on time is if the max(state change date) < Max(iteration Date).  I think i have this part fine.  its just the part of if 'Promotions" is there, end the statement and skip the part to look for 'Closed'

 

Status spc 2 = 
VAR _maxSC = CALCULATE(MAX('SCR'[Iteration End Date]))
VAR _maxP = CALCULATE(MAX('SCR'[State Change Date]),'SCR'[State]="Promotions")
VAR _maxCL = CALCULATE(MAX('SCR'[State Change Date]),'SCR'[State]="Closed")
VAR _CP = IF(_maxP <= _maxSC, 1,0)
VAR _CC = IF(_maxCL <= _maxSC, 1,0)
return

SWITCH(TRUE(),

    IF(
    SCR[State], "Promotions", "ComP"
    ),
    IF(
    SCR[State], "Closed", "ComC"
    )
)

 

this code gives me the following error.
Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Need Help

4 Replies

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    You dont use IF inside a switch
    and you can reference SCR[State] because it will return  table value.
    Yoy need to use SELECETEDVALUE to get sclar value

    See example
    please click accept solution button and the thumbs up button

    thanks

     

    Example =
    Var mystate = SELECETEDVALUE(SCR[State])
    RETURN

    SWITCH(TRUE(),
    mystate = "Promotions", "ComP",
    mystate = "Closed", "ComC"
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks.  Its almost there.  When i use that code, and it does the check on Promotions, it then proceeds to run the check on Closed also, so i get both in the results when i just want it to return just Promotions and if Promotions is not there, run it for Closed.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Status spc 2 = 
VAR _maxSC = CALCULATE ( MAX ( 'SCR'[Iteration End Date] ) )
VAR _maxP = CALCULATE ( MAX ( 'SCR'[State Change Date] ), 'SCR'[State] = "Promotions" )
VAR _maxCL = CALCULATE ( MAX ( 'SCR'[State Change Date] ), 'SCR'[State] = "Closed" )
VAR _CP = IF ( _maxP <= _maxSC, 1, 0 )
VAR _CC = IF ( _maxCL <= _maxSC, 1, 0 )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE(SCR[State]) = "Promotions", "ComP",
        SELECTEDVALUE(SCR[State]) = "Closed", "ComC"
    )

    Is this the result you expect? If not, please advise what output you expect.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I would like for it to look for the "Promotions" first, run its check on if its completed on time or not.  If there is not a "Promotions" do the same for "Closed"  but i do not want it t check on "Closed" If it found a "Promotions".  I am having to calculate a completion percentage of all work items.  and they consider a work item complete if it hit Promotions or closed before the iteration end date.  if it counts both "Promotions" and "Closed" for a single work item, ill end up with a double count that may not even be close to what i need.

      thanks!