Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count Distinct unique values by Month

Hello

 

I am trying to create a calculated column in a table that displays total number of distinct unique values per month. For instance, in the below table, there are 3 unique values - A1, B2 and B3 in the period of Jan 2017. So I need a count of 3 to be displayed in my count column.

 

DateUnique valueCount required
31/01/2017A13
31/01/2017B23
31/01/2017A13
31/01/2017B33
31/01/2017A13
28/02/2017A12
28/02/2017A12
28/02/2017B22
28/02/2017B22
31/03/2017A14
31/03/2017B24
31/03/2017B34
31/03/2017C34

 

Is it possible to calculate the 'Count required' column through DAX?

Any help will be appreciated.

 

Thanks
Shreyas

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below steps:

    Create a Month column:

    Month = MONTH('Table2'[Date])

    Create below measure:

    Measure = CALCULATE(DISTINCTCOUNT(Table2[Unique value]),FILTER(ALL('Table2'),'Table2'[Month]=MAX('Table2'[Month])))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below steps:

    Create a Month column:

    Month = MONTH('Table2'[Date])

    Create below measure:

    Measure = CALCULATE(DISTINCTCOUNT(Table2[Unique value]),FILTER(ALL('Table2'),'Table2'[Month]=MAX('Table2'[Month])))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He