Forum Discussion
DAX Conversion for IF/AND Statement
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
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
1 Reply
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