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

     

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