topic Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ?? in DAX Commands and Tips

DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 16:31:45 GMT

Hello to all,

I'm writing to you because my query below doesn't work anymore for some time (an update of power bi maybe ?). Indeed I created this query to count the number of non empty values in a column. This query was working, it was counting the values at 0 as a value but recently it considers the values at 0 as a "blank".

Here is my query:

SUMX(
SUMMARIZE(
VALUES(dim_measure_faucon[libelle_mesure]),dim_measure_faucon[libelle_mesure],
"Pmoy",
CALCULATE(DISTINCTCOUNTNOBLANK(fact_measure_points_faucon[dt (10 min)]),FILTER(fact_measure_points_faucon,fact_measure_points_faucon[value]<>BLANK())
),
[Pmoy]
)

Can you please explain me why?

Thanks in advance,

Joël

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

AlexisOlson — Wed, 07 Jul 2021 16:38:18 GMT

Can you give an example of a table where this happens so we can try to reproduce what you describe?

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 16:44:31 GMT

Hello AlexisOlson

Unfortunately I can't share my pbix but here is my table "fact_points_measures_faucon" in the image below :

And here is my "dim_measure_faucon" table

I hope this can help you !

Thanks in advance !

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 16:59:06 GMT

First, the expression you've given is syntactically incorrect. Second, you should never, under any circumstances, use SUMMARIZE to do calculation in it. This function is predictable and relatively fast only when doing grouping with it. Do any other thing with it and you're out of luck. It means that even if today the function seems to do what you expect it to, tomorrow it will not be and you won't even notice it.

Please remove the calculation from under SUMMARIZE and use the ADDCOLUMNS/SUMMARIZE hybrid instead. However, I can see you don't even need SUMMARIZE in there for grouping. A simple VALUES or DISTINCT is enough.

I can't do anything more for you since the formula itself is not even correct syntactically, as I said.

By the way, if you want to really see how complex the SUMMARIZE function really is and how unpredictable its output can be, please get familiar with this: All the secrets of SUMMARIZE - SQLBI

And here's the closing remarks from AlbertoFerrari:

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 17:08:21 GMT

You can try this:

SUMX( VALUES(dim_measure_faucon[libelle_mesure]), CALCULATE( DISTINCTCOUNTNOBLANK(fact_measure_points_faucon[dt (10 min)]), KEEPFILTERS( NOT ISBLANK( fact_measure_points_faucon[value] ) ) ) ) // Bear in mind that T[C] <> BLANK // also means T[C] <> 0 since // BLANK = 0.

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 17:25:43 GMT

Hello Anonymous

Yes, it works. I didn't know that "summarize" was a function not to be used for calculations, when should it be used? Secondly, what do you call the hybrid function "ADDCOLUMNS/SUMMARIZE" please?

Thank you for your generous help, it helped me a lot!

Joël

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 17:44:07 GMT

Please read thoroughly all my replies and always follow the links that I place in there. Then everything will become clear.

Re: DISTINCTCOUNTNOBLANK count the 0 as blank why ??

Anonymous — Wed, 07 Jul 2021 19:22:09 GMT

Sorry Anonymous I didn't see the whole of your answer,

Thanks again for your help,

Have a nice day,

Joël