Forum Discussion

SarahHope's avatar
SarahHope
Helper II
3 years ago
Solved

Syntax for Calculated Column to Filter results from three fields using OR logic

Hello!

I'm brand new to Power BI. I'm trying to create a calculated column containing a filter for three other columns in the same data sheet using OR logic.


I want a calculated column showing me all changes that will need post implementation review.  I want to name that column "PIR" and I want a Boolean result in that column

 

Change Type = "Emergency Change"

OR
Expedited = True

OR

Resulted in Outage = True

 

I've tried a number of configurations and tried to follow examples I have found in help forums.  But I'm just not getting it. 

This is what I have for starters:

PIR = FILTER('Full SharePoint Export'[Expedited]=True || 'Full SharePoint Export'[Change Type] = "Emergency Change" || 'Full SharePoint Export'[Resulted in Outage] = TRUE)
My syntax is incorrect.
I could really use a little help.
Thanks!
Sarah

 

"Calculated Column"
calculated
Filter
logic
OR
  • SarahHope's avatar
    SarahHope
    3 years ago

    FreemanZ - Thank you SO much!  That gets me much closer. 

    What is the significance of the TRUE, TRUE, FALSE at the end?

    I like to understand why I am doing what I'm doing.

     

    Apparently, since the 'Change Type' field is not boolean and the other two fields are,  FILTER is giving me an error.

    So, I am trying to make another calculated column called "Emergency" that returns "True" for any change where Change Type = "Emergency Change"

     

     I have tried this:

    Emergency = CONTAINS(FILTER('Full SharePoint Export'[Change Type]),"Emergency Change")
    But am getting "too few arguements were passed to the FILTER function."
     
    I'll keep researching and let you know if I figure it out.  I'd be happy for advice too!
     
    Thanks,
    Sarah

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi SarahHope 

    try like:

    PIR = 
    IF('Full SharePoint Export'[Expedited]=TRUE || 'Full SharePoint Export'[Change Type] = "Emergency Change" || 'Full SharePoint Export'[Resulted in Outage] = TRUE, TRUE, FALSE)
    • SarahHope's avatar
      SarahHope
      Helper II
      3 years ago

      FreemanZ - Thank you SO much!  That gets me much closer. 

      What is the significance of the TRUE, TRUE, FALSE at the end?

      I like to understand why I am doing what I'm doing.

       

      Apparently, since the 'Change Type' field is not boolean and the other two fields are,  FILTER is giving me an error.

      So, I am trying to make another calculated column called "Emergency" that returns "True" for any change where Change Type = "Emergency Change"

       

       I have tried this:

      Emergency = CONTAINS(FILTER('Full SharePoint Export'[Change Type]),"Emergency Change")
      But am getting "too few arguements were passed to the FILTER function."
       
      I'll keep researching and let you know if I figure it out.  I'd be happy for advice too!
       
      Thanks,
      Sarah
    • SarahHope's avatar
      SarahHope
      Helper II
      3 years ago

      FreemanZ I've got it!

       

      Emergency = CONTAINSSTRING('Full SharePoint Export'[Change Type],"Emergency Change")
      This works!

       

      I also learned that the field for "Resulted in Outage" looked like True/False, but it wasn't.  I changed it. 

       

      Everything works.

      I'm still wondering what the TRUE, TRUE, FALSE) at the end of the statement you provided means.

      That's not urgent, but I would like to know.  

       

      Also, I want to accept the solution with a caveat - Can I do that?

      "This solution works as long as all the fields are True/False"