cancel
Showing results for
Did you mean:

## Count distinct values Issue with non unique

Hello

I have a simple issue:

Source table

Aggregated table:

1 ACCEPTED SOLUTION

Accepted Solutions
Resident Rockstar

try the following formula:

``countdistinctnonblank = CALCULATE(DISTINCTCOUNTNOBLANK(emails[link]), emails[link]<>"" && NOT ISBLANK(emails[link]))``
7 REPLIES 7
Solution Sage
``````[# Distinct Non-Blank Links] =
calculate(
keepfilters( not isblank( T[Link] ) )
)``````
Super User IV

@ignas , Try a new measure like

Proud to be a Super User!

Resident Rockstar

try the following formula:

``countdistinctnonblank = CALCULATE(DISTINCTCOUNTNOBLANK(emails[link]), emails[link]<>"" && NOT ISBLANK(emails[link]))``

Thanks a lot for such a quick response.

I tried all of your solutions and only @themistoklis solution gave me the correct results:

@themistoklis Do you know why I need to wrap around CALCULATE? Why just simple function DISTINCTCOUNTNOBLANK does not have correct results?

Resident Rockstar

Hello @ignas

I used 'Calculate' function as I had to apply filters (e.g. <>"" && NOT IS BLANK) in the formula.

You can only apply filters in a formula through the CALCULATE function.

https://docs.microsoft.com/en-us/dax/calculate-function-dax

@themistoklis
I understand how CALCULATE function works. I just cannot understand why I cannot use only DISTINCTCOUNTNOBLANK function in this case? Why if I only use DISTINCTCOUNTNOBLANK it still gives me a value of 4 which means it still counts blanks.

Resident Rockstar

It has to do with your background data. PowerBI treats differently Blank, NULL and EMPTY values.

Maybe the following 2 articles will give you the answer:

https://www.sqlbi.com/articles/blank-handling-in-dax/

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors