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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Lucifer2019
Helper I
Helper I

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
Lucifer2019
Helper I
Helper I

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

 

@Lucifer2019 

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!




see above

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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