Count Rows based on NULL/ NOT-NULL in multiple columns

Question & Answer
, ,
1

I have the following dataset and I need to count rows in a few different scenarios.

Client. Lead Alliance Lead Architect
aaaa Bryan Jennifer
bbb Lance Daniel
cccc Jesse Denise

  1. Count rows where there’s a value in all three columns (Lead, Alliance Lead and Architect)
  2. Count rows where there’s a value in the Lead or Alliance Lead (could be a value in any of those two columns) but null in the architect column
  3. Count rows where both Lead or Alliance Lead are null but there’s a value in the architect column
  4. Count rows with specific values on those columns

I created a calculated field and was trying to use countIf(isNull({Lead} OR isNull(Alliance Lead} AND isNotNull({Architect}))) but is not working. What would be the proper format for that? Also if I have specific values on those rows that I want to count like per name for example what would be the best way to do that?

Appreciate any help as you can see I am new to all of this.Thanks!

2

Hi @deb21,

Your syntax is incorrect. You’re giving QuickSight your condition but you’re not telling it what to count. Assuming you what to count clients, you should change it to this:

countIf(Client, isNull({Lead} OR isNull(Alliance Lead} AND isNotNull({Architect})))

If you want to count clients where Lead is a particular name, you can add a condition like Lead = ‘Bob’ to your countIf function.

4

Thank you! I am still getting an error but won’t point out to what is incorrect. If I am just trying to count the number of rows where those combinations exist why do I need point to another field?

5

There’s no function to count number of rows. If your visual contains only those 4 columns like in your example, counting the number of clients should give you the same result.

I found you were missing brackets in some places. Can you try this?

countIf(Client, isNull({Lead}) OR isNull({Alliance Lead}) AND isNotNull({Architect}))
1 Like
6

Thank you! I had to use the ID which was the unique value but it works now.