DISTINCTCOUNTNOBLANK count the 0 as blank why ??

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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: 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

Thanks again for your help,

Have a nice day,

Joël

[AlexisOlson]

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

[Anonymous]

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 !