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

Counting a distinct value in multiple columns

Hello PBI community! I was wondering how I can count a distinct value on multiples column at the same time. As In the example below, I need a measure to count how many zeros (total) there are on columns, 1,2 3 (the answer should be 4):

 

JulioYYC_0-1665858639136.png

Thnaks!

 

1 ACCEPTED SOLUTION
deevaker
Resolver I
Resolver I

Not sure if i missed to understand, but if you need to add all the count of zeros, you can write this 

Measure =
CALCULATE(Count('Table'[Column1]),'Table'[Column1]=0)+
CALCULATE(Count('Table'[Column2]),'Table'[Column2]=0)+
CALCULATE(Count('Table'[Column3]),'Table'[Column3]=0)

below is the screenshot for reference

deevaker_0-1665861512606.png

 

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, right click on the first column's heading and select "Unpivot Other Columns".  Write this measure

Count = calculate(countrows(Data),Data[Value]=0)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you Ashish, I wanted to avoid this step of unpivoting the columns.. Deevaker's solution below worked better for me. Thanks for replying. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
deevaker
Resolver I
Resolver I

Not sure if i missed to understand, but if you need to add all the count of zeros, you can write this 

Measure =
CALCULATE(Count('Table'[Column1]),'Table'[Column1]=0)+
CALCULATE(Count('Table'[Column2]),'Table'[Column2]=0)+
CALCULATE(Count('Table'[Column3]),'Table'[Column3]=0)

below is the screenshot for reference

deevaker_0-1665861512606.png

 

Anonymous
Not applicable

Hi Deevaker, it worked perfectly! many thanks!

Anonymous
Not applicable

Yes you can do tyhis directly. I did not find a better solution to do it

Anonymous
Not applicable

Hi

COUNT0 =
var col1=COUNTROWS((filter(Feuil4,Feuil4[Column 1]=0)))
var col2=COUNTROWS((filter(Feuil4,Feuil4[Column 2]=0)))
var col3=COUNTROWS((filter(Feuil4,Feuil4[Column 3]=0)))
return
col1+col2+col3

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.