Forum Discussion

ignas's avatar
ignas
Advocate II
5 years ago
Solved

Count distinct values Issue with non unique

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 

Need Help
Show and Tell
Tips and Tricks
  • themistoklis's avatar
    themistoklis
    5 years ago

    ignas 

     

    try the following formula:

     

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

7 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    ignas 

     

    try the following formula:

     

    countdistinctnonblank = CALCULATE(DISTINCTCOUNTNOBLANK(emails[link]), emails[link]<>"" && NOT ISBLANK(emails[link]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    [# Distinct Non-Blank Links] =
calculate(
    distinctcount( T[link] ),
    keepfilters( not isblank( T[Link] ) )
)
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    ignas , Try a new measure like

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

  • ignas's avatar
    ignas
    Advocate II
    5 years ago

    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?

      • ignas's avatar
        ignas
        Advocate II
        5 years ago

        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.