DAX Conversion for IF/AND Statement

[sakhtar]

Can someone please help me convert the following if statement in DAX. My conversion isnt producing the correct results, it may be because I dont know how to add the AND statment.

Also, how do you add an else make everything else statement at the end ; for example, below I put else "Out of Scope" which means if none of the above apply mark it as out of scope.

Thank you!

case 
when [Contract Action]="Closed" then "Contract Closed"
when [Contract Action]="Cancelled" then "Contract Closed"
when ([Contract Action]="Approved to Close") and ([Days to Contract Expiration]<-180) then "Past Due"
when ([Contract Action]="Approved to Close") and ([Days to Contract Expiration]<-90) then "Off Track"
when ([Contract Action]="Approved to Close") and ([Days to Contract Expiration]<0) then "On Track"
when ([Contract Action]="Approved to Close") and ([Days to Contract Expiration]>=0) then "Projected"
when ([Contract Action]="Approved to Extend") and ([Days to Contract Expiration]<0) then "Past Due"
when ([Contract Action]="Approved to Extend") and ([Days to Contract Expiration]<15) then "Off Track"
when ([Contract Action]="Approved to Extend") and ([Days to Contract Expiration]<45) then "On Track"
when ([Contract Action]="Approved to Extend") and ([Days to Contract Expiration]<=60) then "Projected"
when ([Contract Status]="Open") and ([Days to Contract Expiration]<0) then "Past Due"
when ([Contract Status]="Open") and ([Days to Contract Expiration]<16) then "Off Track"
when ([Contract Status]="Open") and ([Days to Contract Expiration]<46) then "On Track"
when ([Contract Status]="Open") and ([Days to Contract Expiration]<61) then "Projected"
when ([Contract Status]="Expired") and ([Days to Contract Expiration]<0) then "Past Due"
when ([Contract Status]="Expired") and ([Days to Contract Expiration]<16) then "Off Track"
when ([Contract Status]="Expired") and ([Days to Contract Expiration]<46) then "On Track"
when ([Contract Status]="Expired") and ([Days to Contract Expiration]<61) then "Projected"
when [Contract Status]="Letter Sent" then "On Track"

else "Out of Scope"
end

[MFelix · Accepted solution]

Hi @sakhtar ,

I'm assuming you are creating a new column so you should use the following sintax:

Column =
SWITCH (
    TRUE ();
    Table[Contract Action] = "Closed"; "Contract Closed";
    Table[Contract Action] = "Cancelled"; "Contract Closed";
    ( Table[Contract Action] = "Approved to Close"
        && Table[Days to Contract Expiration] < -180 ); "Past Due";
    ( Table[Contract Action] = "Approved to Close"
        && Table[Days to Contract Expiration] < -90 ); "Off Track";
    ( Table[Contract Action] = "Approved to Close"
        && Table[Days to Contract Expiration] < 0 ); "On Track";
    ( Table[Contract Action] = "Approved to Close"
        && Table[Days to Contract Expiration] >= 0 ); "Projected";
    ( Table[Contract Action] = "Approved to Extend"
        && Table[Days to Contract Expiration] < 0 ); "Past Due";
    ( Table[Contract Action] = "Approved to Extend"
        && Table[Days to Contract Expiration] < 15 ); "Off Track";
    ( Table[Contract Action] = "Approved to Extend"
        && Table[Days to Contract Expiration] < 45 ); "On Track";
    ( Table[Contract Action] = "Approved to Extend"
        && Table[Days to Contract Expiration] <= 60 ); "Projected";
    ( Table[Contract Status] = "Open"
        && Table[Days to Contract Expiration] < 0 ); "Past Due";
    ( Table[Contract Status] = "Open"
        && Table[Days to Contract Expiration] < 16 ); "Off Track";
    ( Table[Contract Status] = "Open"
        && Table[Days to Contract Expiration] < 46 ); "On Track";
    ( Table[Contract Status] = "Open"
        && Table[Days to Contract Expiration] < 61 ); "Projected";
    ( Table[Contract Status] = "Expired"
        && Table[Days to Contract Expiration] < 0 ); "Past Due";
    ( Table[Contract Status] = "Expired"
        && Table[Days to Contract Expiration] < 16 ); "Off Track";
    ( Table[Contract Status] = "Expired"
        && Table[Days to Contract Expiration] < 46 ); "On Track";
    ( Table[Contract Status] = "Expired"
        && Table[Days to Contract Expiration] < 61 ); "Projected";
    Table[Contract Status] = "Letter Sent"; "On Track";
    "Out of Scope"
)

If it's a measure it need to be redone in a different way, because measure need to have aggregators.

Regards,

MFelix