cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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):

Thnaks!

1 ACCEPTED SOLUTION
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

7 REPLIES 7
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
Helper II

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors