Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Count repeated values only once

I have a table as shown in the image with opportunity codes that are unique and do not repeat and I also have a column that is the ID_Cliente that can be repeated several times.

I want to get a calculated column that puts me "1" for each id_cliente counted only 1 time, but it is counting me only the only ones with this DAX formula

CALCULATE(DISTINCTCOUNT(Opportunities[codOpportunity]),ALLEXCEPT(Opportunities, Opportunities[CustomerID]))

I want you to tell me both the only ones that are not repeated, as well as the repeated but counted only 1 time. So the table would look as shown in the image. I appreciate any ideas or help.

  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi Syndicate_Admin ,
    Update your counting formula for :

    Column =
    if(CALCULATE ( COUNT ( 'Table'[id] ), ALLEXCEPT ( 'Table','Table'[id] ) )=1,1,blank())
    *Whithout "if" it will just count repeats of every "id".

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!



2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi Syndicate_Admin ,
    Update your counting formula for :

    Column =
    if(CALCULATE ( COUNT ( 'Table'[id] ), ALLEXCEPT ( 'Table','Table'[id] ) )=1,1,blank())
    *Whithout "if" it will just count repeats of every "id".

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!



  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    Hi Could you help me with this request, I'm trying to place this formula, if(CALCULATE ( COUNT ( 'Table'[id] ), ALL except ( 'Table','Table'[id] ) )=1,1,blank()), but I can't find the count function and the third criterion of the if function (i.e. the blank one) doesn't work.