Distinct count over 3 columns

[MRoyW]

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.

Name	Acc no	Year
Josh	1	Dec23
Kalvin	20	Dec20
Basey	3	Dec21
Kalvin	20	Dec20
Kalvin	20	Dec21

[TomMartens · Accepted solution]

Hey @MRoyW ,

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

[MRoyW]

@TomMartens  @Shreeram04  thank you both

[Shreeram04 · Accepted solution]

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

[TomMartens · Accepted solution]

Hey @MRoyW ,

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