cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Count distinct values Issue with non unique

Hello

I have a simple issue:

Source table

ignas_0-1603791131733.png

Aggregated table:

ignas_1-1603791404223.png

How to get only a count for a link that has a non-null value? In  this case, I should get 3: link1+link2+link4=3

Power BI file 

1 ACCEPTED SOLUTION

Accepted Solutions
Resident Rockstar
Resident Rockstar

@ignas 

 

try the following formula:

 

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

View solution in original post

7 REPLIES 7
Solution Sage
Solution Sage

[# Distinct Non-Blank Links] =
calculate(
    distinctcount( T[link] ),
    keepfilters( not isblank( T[Link] ) )
)
Super User IV
Super User IV

@ignas , Try a new measure like

calculate(distinctcount(Table[link]),not(isblank(Table[link])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Resident Rockstar
Resident Rockstar

@ignas 

 

try the following formula:

 

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

View solution in original post

Advocate II
Advocate II

@themistoklis @amitchandak @daxer 

Thanks a lot for such a quick response. 

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

ignas_0-1603823207998.png

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

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.

@ignas 

 

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/

 

https://www.excelguru.ca/blog/2018/10/04/nuthin-aint-nuthin-in-power-query/#:~:text=There%20are%20tw....

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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