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 doing wrong? 

=

IF('Table'[Column A] = "Subject"
 &&'Table'[Column B] = "Substantiated"
 &&'Table' [Column B] = "Unsubstantiated",
 &&'Table' [Column B] = "Insufficient Data"),
COMBINEVALUES ("-", [Column C], [Column D], BLANK ()))
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

     

5 Replies

  • 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

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    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

     

    • StoryofData's avatar
      StoryofData
      Helper III
      3 years ago

      Follow up question Philip, the syntax works fine if it's in table format, however when I try to do a distinct count on it, the number is uncorrect in my screen shot below, distinct count should 5

       

      All Subjects (Participant Closed) =
      IF('Table'[IemRoleInCase] = "Subject"
      && ('Table'[IemOutcome] = "Unsubstantiated"
      || 'Table'[IemOutcome] = "Substantiated"
      || 'Table'[IemOutcome] = "Insufficient Data"),
      [IemCaseNumber]&" - "&[EmplId])