Forum Discussion

dandywong's avatar
dandywong
Frequent Visitor
8 years ago

calculate distinct count filter by not zero

Hi there,

 

I'm trying to figure out how to calculate, distinct count and filter based on a column having a value >= 1.

 

How can I filter out the rows based on there not being a value in the column? Basically, if the cell IS BLANK, then it should show up in this table.

 

 Image

Need Help

6 Replies

  • dandywong's avatar
    dandywong
    Frequent Visitor
    8 years ago

    Sorry, if cell IS BLANK, it should not show up in this table

    • dedelman_clng's avatar
      dedelman_clng
      Icon for Community Champion rankCommunity Champion
      8 years ago

      You will want to use something like

       

      FILTER(Tab, NOT(ISBLANK(Tab[blankable column])

       

      as a parameter on CALCULATE.

       

      Also, be aware that sometimes a value is BLANK, sometimes it is just ""

       

      Hope this helps.

      David

      • dandywong's avatar
        dandywong
        Frequent Visitor
        8 years ago

        Thanks @ dedelman_clng, but it did something weird with  the count of the column or did I not get the script right?

         

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    hi, dandywong

         It seems that you want to distinct count by Grouping。

    You may try to use EARLIER Function in your formula

    for example

    Count 2 = CALCULATE(DISTINCTCOUNT(Table2[Column2]),FILTER(Table2,NOT(ISBLANK(Table2[Column2]))))
    Count 3 = CALCULATE(DISTINCTCOUNT(Table2[Column2]),FILTER(Table2,Table2[Column1]=EARLIER(Table2[Column1])&&NOT(ISBLANK(Table2[Column2]))))

    Result:

     

    If not your case, please share your sample pbix and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

     

     

     

    Best Regards,

    Lin