Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

How to count rows based on a column that has data and whether or not an additional column is null

Hi, 

I'm trying to replicate a Countif function in Excel that will count the number of rows based on a item in one column, then I need to see if another column has any data or is null for that row.  Based on the combination of the two colums having data on a row, then I need to count the row to get a count of rows that are similar.

The project I'm working on has a total of 10 columns that are looked at to see if there is data in the column and if it does its considered a 2 intead of a 1.  A second column is then created to count a row with not null fields in 3 columns.  I need to take this out to a total of 10 columns that we look at to see if the column is not null.

In Excel it would just be =COUNTIFS(Cases!P:P,"*",Cases!Z:Z,"*",Cases!AJ:AJ,"",Cases!AT:AT,"",Cases!BD:BD,"",Cases!BN:BN,"",Cases!BX:BX,"",Cases!CH:CH,"",Cases!CR:CR,"",Cases!DB:DB,"")

4 REPLIES 4
Anonymous
Not applicable

YOu can see here in my data table the highlighted columns, I need to count how many columns have an answer in them.  Row 2 has 4 answers, row 3 has 2 answers, row 4 has one answer, etc. Row 12 has zero answers

I need to find a way to simply count by row, how many of these columns have an answer.  There are up to 10 columns that will have an answer.  Then I need to sum up each colmn number, so like column P has 10 answers, column z has 5 answers, AJ has 4 answers, AT has 2 answers.

Lucifer2019_0-1684330920505.png

 

@Anonymous 

in my sample data,

select first column and unpivot other columns

1.png

in the value column, unselect null

2.png

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

could you pls provide some sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

see above

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.