Counting a distinct value in multiple columns

[Anonymous]

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!

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

You are welcome.

[deevaker · Accepted solution]

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

[Anonymous]

Hi Deevaker, it worked perfectly! many thanks!

[Anonymous]

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

[Anonymous]

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