cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors