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 DA...
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"

     

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"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I am fairly new to Power Bi does "[]" indicate a table? All of my variables are columns in one table.

     

    • az38's avatar
      az38
      Community Champion
      6 years ago

      Anonymous 

      [] its a column name inside your table.

      Table[Column] - full syntax

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you so much! This has been so helpful.

         

        Do you know how I culd use this new column in an if then statement sort of thing but using a DAX function instead? Like if = court then [Court Statement] if = Prosec then [Prosec Statement], else unknown.

         

        I would do the if then in power query mode but working from home I lost access to the server adn can't update the data. So no power query for me today.