Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Distinct count over 3 columns

Hi,
I'm trying to add a new calculated column for the count of customer accounts per user (Name) in my data set.
How do I do this?
I only need the count for specific years. Like in the example table below, Kalvin would show 2 in the new column for 2020 and 1 for 2021.

NameAcc noYear
Josh1Dec23
Kalvin20Dec20
Basey3Dec21
Kalvin20Dec20
Kalvin20Dec21
Need Help
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    2 years ago

    Hey Anonymous ,

     

    You can use DAX statement to create a calculated column:

    Column = 
var currentName = 'Table'[Name]
var currentYear = 'Table'[Year]
return
COUNTROWS(
    FILTER(
        'Table'
        , 'Table'[Name] = currentName && 'Table'[Year] = currentYear
    )
)

    The table will look like this (do not wonder about the year column, this happened by pasting your data into a table):

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

     

  • Shreeram04's avatar
    Shreeram04
    2 years ago

    Hi TomMartens 

     

    Please find the below measure and screenshot for your reference.

    Customer Count =
    CALCULATE(
        COUNTROWS('AA'),
        ALLEXCEPT('AA', 'AA'[Name], 'AA'[Year])
    )


    If this post helps, then please consider Accept it as the solution 

    Thanks,

    Hari R

     

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey Anonymous ,

     

    You can use DAX statement to create a calculated column:

    Column = 
var currentName = 'Table'[Name]
var currentYear = 'Table'[Year]
return
COUNTROWS(
    FILTER(
        'Table'
        , 'Table'[Name] = currentName && 'Table'[Year] = currentYear
    )
)

    The table will look like this (do not wonder about the year column, this happened by pasting your data into a table):

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

     

  • Shreeram04's avatar
    Shreeram04
    Resolver III
    2 years ago

    Hi TomMartens 

     

    Please find the below measure and screenshot for your reference.

    Customer Count =
    CALCULATE(
        COUNTROWS('AA'),
        ALLEXCEPT('AA', 'AA'[Name], 'AA'[Year])
    )


    If this post helps, then please consider Accept it as the solution 

    Thanks,

    Hari R