Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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!

 

Need Help
  • deevaker's avatar
    deevaker
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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
  • deevaker's avatar
    deevaker
    Resolver I
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Deevaker, it worked perfectly! many thanks!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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