Forum Discussion
DISTINCTCOUNTNOBLANK
Power BI and Kusto(ADX) give the same number of line in my raw data : 4637.
But, when I want distinct values, I get different results.
***********************************************
Power bi gives : 1699
mesure = DISTINCTCOUNTNOBLANK(Table[ID])
***********************************************
***********************************************
Kusto gives : 1703
Table
| where ID<>""
| summarize mesure = count_distinct(ID)
***********************************************
So I importe the raw data : 4637 lines in Excel and delete duplicate values and obtain : 1703 (kusto result).
In Power bi, I also create a table to display the ID column. So normaly It should display 1699 values. But when I export the table in excel, I obtain 1703 rows.
It is important to note that I didn't apply a filter in power bi and in Power query. So I don't understand where is the issue.
DISTINCTCOUNTNOBLANK does not count the BLANK value.
It only works for numeric columns. It does not work for text columns.Learn how here ....
https://www.youtube.com/watch?v=Tl56PDF0aIc
Also note
Excel is case sensitive.
Power BI is not case sensitve.
For example if you have
- Apples
- APPLES
- APPLES
- aPPles
The Excel will have 3 distinct values, but Power BI will have 1
Please click thumbs up because I have tried to help.
Then [click accept solution] if it helps
I want to help you but your description is too vague. Please provide a small example
You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming
* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy).
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble.
* Please click the thumbs up button for these helpful hints and tips. Thank you.
Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.
Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.
10 Replies
Hi gerard-kouadio ,
This is a really interesting case — thanks for laying it out so clearly.
What you're seeing is likely due to how Power BI visuals handle blank or non-printable values, especially when using DISTINCTCOUNTNOBLANK.
Here’s what might be happening:
- Your ID column might contain invisible characters (like zero-width spaces, non-breaking spaces, or line breaks) that Excel and Kusto treat as valid values, but Power BI visuals might group together or ignore in certain contexts.
- The DISTINCTCOUNTNOBLANK measure might be excluding values that are technically not blank but are treated as such in visuals (e.g., whitespace-only strings).
A few things you can try:
Create a calculated column to clean the ID values:
CleanedID = TRIM(SUBSTITUTE(SUBSTITUTE([ID], UNICHAR(160), ""), UNICHAR(8203), ""))
Then use:
DISTINCTCOUNTNOBLANK(Table[CleanedID])
Use a table visual and add a filter like LEN([ID]) > 0 to see if any values are technically present but visually empty.
Also, check if your model has auto-detect relationships or row-level security that might be silently filtering out some rows.
Let me know if you want help inspecting a sample of the data — happy to dig deeper.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was supported by AI for translation and text editing.
DISTINCTCOUNTNOBLANK does not count the BLANK value.
It only works for numeric columns. It does not work for text columns.Learn how here ....
https://www.youtube.com/watch?v=Tl56PDF0aIc
Also note
Excel is case sensitive.
Power BI is not case sensitve.
For example if you have
- Apples
- APPLES
- APPLES
- aPPles
The Excel will have 3 distinct values, but Power BI will have 1
Please click thumbs up because I have tried to help.
Then [click accept solution] if it helps
I want to help you but your description is too vague. Please provide a small example
You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming
* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy).
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble.
* Please click the thumbs up button for these helpful hints and tips. Thank you.
Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.
Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.The apple example might be the reason for the discrepancy. In Power Query, those text strings are treated as separate values, but once loaded into the model, VertiPaq keeps only the first version it sees — so they all end up being treated as a single distinct value.
burakkaragoz is correct that invisible characters will be counted as unqiue differences.
Sorry to correct burakkaragoz but I feel he is making some mistakes and perhaps he is using AI to answer questions?Invisible characters would not explain the different counts in EXCEL and Power BI.
Also TRIM removes spaces where as CLEAN removes invisible control characters.
It is therefore best pratice to use a CLEAN and then a TRIM in Power Query for data cleansing.
Rather than use TRIM and SUBSTITUTE in DAX.
It just makes more sense to prepare your data before using it.
Learn how herehttps://www.youtube.com/watch?v=jvbVVDyGpEs
That guy uses AI and most of the time wouldn't even validate his reponses. Called him out several times but just ignored me.
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank youHi gerard-kouadio ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked.
If not, Please feel free to contact us if you have any further questions.
Thanks and regards
Hi gerard-kouadio
Did you try my method?Please click thumbs up and the [accept as solution] buttons.
It is polite and sensible to thank helpers this way. 😀
