Supposable a bug in DISTINCTCOUNTNOBLANK

[Nick_123]

Hello, Folks!
I had a measure 
DISTINCTCOUNTNOBLANK( f_Query_1[agentid] ) , which counted  unique number of agents (excluding blanks),
But since I like to double check everything, i wrote this:

CALCULATE(

    DISTINCTCOUNT( f_Query_1[agentid] ) ,

    FILTER(

        f_Query_1 ,

        f_Query_1[agentid] <> BLANK()

    )

)
which gave me a different result! (and a correct one).

Both DISTINCTCOUNT and DISTINCTCOUNTNOBLANK have the same description in DAX.
Do they behave the same way?
DISTINCTCOUNTNOBLANK did not remove blanks in the result?
I think the name of the syntaxs says so or it is missleading.

[v-agajavelly]

Hi @Nick_123 

Just checking in once more, we haven’t heard back yet. If your issue has been resolved, a quick update or marking the accepted solution would be really helpful for others referencing this thread in the future.

If you're still facing any challenges, feel free to share more details we’re here to help!
Thanks again for being part of the Microsoft Fabric Community.

Best regards,
Akhil

[v-agajavelly]

Hi @Nick_123 

If your issue is resolved, it would be great if you could share what worked or any key takeaways, it might help others facing a similar situation.

If we don’t hear back, we’ll go ahead and close this thread soon. But if you need help in the future, feel free to post a new question in the Microsoft Fabric Community Forum, we’re always happy to assist.

Thanks again for being part of the community.

Regards,
Akhil.

[Nick_123]

Everybody, Thank you for your support!

[v-agajavelly]

Hi @Nick_123 

Thank you for reaching out to Microsoft Fabric Community Forum.

This DAX has its quirks, and your test is valid. Let’s clarify exactly what’s happening and why DISTINCTCOUNTNOBLANK() may not behave as expected.

The DISTINCTCOUNTNOBLANK(column) is not a standard DAX function.

It is not built into DAX it comes from a custom measure, a Tabular Editor script, a Power BI template, or a third-party tool/extension. Therefore, Its behavior entirely depends on how it was defined, and it might not actually exclude blanks the way you think.

Your manually written measure using CALCULATE + FILTER is correct for excluding blank values from DISTINCTCOUNT.

 
Use this Standard DAX Function:

DISTINCTCOUNT(f_Query_1[agentid])

It Returns the count of distinct non-blank values in the column and it Automatically excludes blanks.

DISTINCTCOUNTNOBLANK(...) This does not exist in native DAX. If it works in your model, it's coming from a custom measure or variable, like.

DISTINCTCOUNTNOBLANK = CALCULATE(DISTINCTCOUNT(f_Query_1[agentid]), NOT(ISBLANK(f_Query_1[agentid])))

A DAX extension like DAX Studio snippets, Tabular Editor custom actions, or model-specific calculated measures.

Now try this Working Version:

CALCULATE(DISTINCTCOUNT( f_Query_1[agentid] ),FILTER( f_Query_1, f_Query_1[agentid] <> BLANK() ))

This is precise and robust. It ensures, blanks are filtered out explicitly and it works even when agentid is coming from related tables or complex models.

If DISTINCTCOUNTNOBLANK() is just a wrapper (like a calculated measure using DISTINCTCOUNT directly), it might still include blanks depending on how it's defined.It’s a naming problem, not a DAX engine bug.

Use your version with CALCULATE + FILTER for full control, and Avoid relying on DISTINCTCOUNTNOBLANK unless you can inspect or verify its definition. When in doubt, wrap the logic yourself — even DISTINCTCOUNT() normally ignores blanks, but if your model has cross-filters or calculated columns with tricky blanks, explicit filtering is safest.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Akhil.

[AlexisOlson]

@v-agajavelly This is clearly an AI-generate response that is also factually inaccurate.

DISTINCTCOUNTNOBLANK is very much a native DAX function, as documented by Microsoft:
DISTINCTCOUNTNOBLANK function (DAX) - DAX | Microsoft Learn

[speedramps]

I hope this helps
https://www.sqlbi.com/tv/distinctcount-distinctcountnoblank-dax-guide/

[AlexisOlson]

DISTINCTCOUNTNOBLANK ( table[column] ) is syntax sugar for

CALCULATE (
    DISTINCTCOUNT ( table[column] ),
    KEEPFILTERS ( NOT ISBLANK ( table[column] ) )
)

Note that the following are not equivalent:

f_Query_1[agentid] <> BLANK()

NOT ISBLANK ( f_Query_1[agentid] )

In particular, the empty string "" is handled differently (see BLANK - DAX Guide).

My guess is that your column has "" (empty string) values rather than null (blank) values. These are eliminated using <> BLANK() but not using DISTINCTCOUNTNOBLANK.

[johnt75]

The name is a bit misleading. DISTINCTCOUNTNOBLANK does not remove all blanks from the column, it only removes a blank value which has been added automatically by Power BI to maintain integrity in a relationship.

If you have a a key column in a dimension table which is on the one-side of a relationship to a column in the fact table and there are values in the fact table which do not appear in the dimension table, Power BI will add a blank row to the dimension table and map that to all the rows in the fact table which have missing values.

As a separate note, it is best practice to not filter an entire table, but only filter the specific columns you need. Also, if you want to check for BLANK you are better using the ISBLANK function, as 0 or the empty string "" equals BLANK, so you could rewrite your code as

Num non-blanks =
CALCULATE (
    DISTINCTCOUNT ( f_Query_1[agentid] ),
    FILTER ( ALL ( f_Query_1[agentid] ), NOT ISBLANK ( f_Query_1[agentid] ) )
)