Counting distinct without blanks of another field

[Applicable88]

Example =

Hello,

Hello,

I have two conditions for a distinctcount of ordernumbers: Duration lower then 60 and no blank fields in the duration column . Since distinctcount gives me no filter options I tried with countx and filter function:

COUNTX(

FILTER('Table1',

Table1[Duration]<> blank()

&& 'Table1'[Duration] < 60

),[Orders]

)

Havin these conditions, I should also count those orders with 0 duration, since they aren't blank. Why it only counts the duration lower then 60 but not the 0's?

Hope someone can clarify.

Thank you very much in advance. 

Best. 

[bcdobbs · Accepted solution]

In DAX BLANK() = 0 evaluates to true which causes your issue.

Have a read of Be Careful When Filtering for Blanks in DAX — ehansalytics

Replace:

Table1[Duration]<> blank()
with NOT ISBLANK ( Table1[Duration] )

If you wanted to use DISTINCTCOUNT you could wrap it in a CALCULATE statement to add the filters you need.

[Syndicate_Admin · Accepted solution]

In my humble opinion and the years I have been for PBI, if it does not affect me to change all whites to -1 I would do it, since I have not been able to find a way for PBI to understand or differentiate which data is white and which data is 0.

I do it like this:

And you'd just have to leave out the -1 values and you're done.

If this post answers your question, please mark as "Accept as a solution" so that other people can also access it.

Best regards

Ivancito

[Applicable88]

@Syndicate_Admin thats a good idea to tidy up the data in the first place. I really didn't know that PowerBi treats blank()=0 like in the information @bcdobbs  provided. 

[bcdobbs · Accepted solution]

In DAX BLANK() = 0 evaluates to true which causes your issue.

Have a read of Be Careful When Filtering for Blanks in DAX — ehansalytics

Replace:

Table1[Duration]<> blank()
with NOT ISBLANK ( Table1[Duration] )

If you wanted to use DISTINCTCOUNT you could wrap it in a CALCULATE statement to add the filters you need.

[Applicable88]

@bcdobbs thank you! Thats what drove me crazy 🙂