Forum Discussion

StoryofData's avatar
StoryofData
Helper III
3 years ago
Solved

CountIf Multiple Columns

I need to combine two columns IF the other two equal to values I specified, it doesn't calculate properly, I am assuming this is due to &&, I tried || instead but it's still incorrect. What I am doi...
Need Help
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    Hi StoryofData 

     

    Download example PBIX file

     

    You can write this 

     

    Result = IF([Col A] = "Subject" && ([Col B] = "Substantiated" || [Col B] = "Unsubstantiated" || [Col B] = "Insufficient Data"), [Col D]&"-"&[Col C])

     

     

    But when you have multiple options to match for an OR condition you can also use the IN function which is a little neater

     

    Result = IF([Col A] = "Subject" && ([Col B] IN {"Substantiated" , "Unsubstantiated" , "Insufficient Data"}), [Col D]&"-"&[Col C])

     

     

     

    Regards

     

    Phil

     

PhilipTreacy's avatar
PhilipTreacy
Super User
3 years ago

Hi StoryofData 

 

Your formula is testing for 3 different values in [Column B] but it can't have 3 different values.  Because you are using && this situation isn't possible.

 

If you use || then that would be possible for [Column B] to have one of those 3 values. So if you have used || and still aren't getting the results you expect I'd need to see your data and the result of the formula to understand what is going wrong.

 

Can you please supply some data so I can understand exactly what it is you are trying to achieve.

 

Regards

 

Phil

 

 

StoryofData's avatar
StoryofData
Helper III
3 years ago

 

Hey, thank you for helping me, my condition is:
Column A = Subject AND

Column B = Substantiated OR Unsubstantiated OR Insufficient Data then combine C and D columns