Count distinct values Issue with non unique

[ignas]

Hello

I have a simple issue:

Source table

Aggregated table:

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 

[ignas]

@themistoklis @amitchandak @Anonymous 

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?

[themistoklis]

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

[ignas]

@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.

[themistoklis]

@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....

[themistoklis · Accepted solution]

@ignas 

try the following formula:

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

[amitchandak]

@ignas , Try a new measure like

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

[Anonymous]

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