cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

 Date Unique value Count required 31/01/2017 A1 3 31/01/2017 B2 3 31/01/2017 A1 3 31/01/2017 B3 3 31/01/2017 A1 3 28/02/2017 A1 2 28/02/2017 A1 2 28/02/2017 B2 2 28/02/2017 B2 2 31/03/2017 A1 4 31/03/2017 B2 4 31/03/2017 B3 4 31/03/2017 C3 4

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

Any help will be appreciated.

Thanks
Shreyas

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

Hi @Shreyas1988 ,

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Microsoft

Hi @Shreyas1988 ,

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Thanks @v-danhe-msft

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors