calculate distinct count filter by not zero

[dandywong]

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.

 

[v-lili6-msft]

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

[dandywong]

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

[dedelman_clng]

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]

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

[dedelman_clng]

It looks like you're trying to create this as a calculated column instead of a measure. Try recreating as a measure and build a table visual instead of using the data preview.

Other than that we would need to see more of your model, data, etc to help troubleshoot further. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[dandywong]