Forum Discussion
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...
Hi StoryofData
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
Hi StoryofData
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
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])