Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Complex If Then Statement

Not sure how to create this IF Then statement, any help would be appreciated. I have two variables that need to be analyzed and a new column created.

 

Would like to know if it can be done using DAX functions or the if then builder in the Query mode.

 

Prosecution Date, Court Date.

 

If both variables are blank = Unknown

If Prosecution is blank and court has a value = "Court"

If Court is blank and Prosecution has a value= "Prosec"

If both variables have a value = "Court" or "Prosec" whichever has the newest date.

 

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    there are 2 options:

    1. DAX using SWITCH()

    Column = SWITCH(TRUE(),
ISBLANK([Prosecution Date]) && ISBLANK([Court Date]), "Unknown",
ISBLANK([Prosecution Date]) && NOT(ISBLANK([Court Date])), "Court",
NOT(ISBLANK([Prosecution Date])) && ISBLANK([Court Date]), "Prosec",
[Prosecution Date]>[Court Date], "Prosec",
"Court"
)

    2. Power QUery Custom Column

    = if [Prosecution Date] = null and [Court Date] = null then "Unknown"
else if [Prosecution Date] = null and [Court Date] <> null then "Court"
else if [Prosecution Date] <> null and [Court Date] = null then "Prosec"
else if [Prosecution Date] > [Court Date] then "Prosec"
else "Court"

     

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    there are 2 options:

    1. DAX using SWITCH()

    Column = SWITCH(TRUE(),
ISBLANK([Prosecution Date]) && ISBLANK([Court Date]), "Unknown",
ISBLANK([Prosecution Date]) && NOT(ISBLANK([Court Date])), "Court",
NOT(ISBLANK([Prosecution Date])) && ISBLANK([Court Date]), "Prosec",
[Prosecution Date]>[Court Date], "Prosec",
"Court"
)

    2. Power QUery Custom Column

    = if [Prosecution Date] = null and [Court Date] = null then "Unknown"
else if [Prosecution Date] = null and [Court Date] <> null then "Court"
else if [Prosecution Date] <> null and [Court Date] = null then "Prosec"
else if [Prosecution Date] > [Court Date] then "Prosec"
else "Court"

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try a new column like

    Switch( true(),
isblank([Prosecution Date]) && isblank([Court Date]) ,"Unknown",
isblank([Prosecution Date]) && not(isblank([Court Date])),"Court",
not(isblank([Prosecution Date])) && (isblank([Court Date])),"Prosec",
max([Prosecution Date],[Court Date])
)