cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Hi @Shreyas1988 ,

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

Create a Month column:

Month = MONTH('Table2'[Date])

1.PNG

Create below measure:

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

Result:

2.PNG

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

 

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.

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Hi @Shreyas1988 ,

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

Create a Month column:

Month = MONTH('Table2'[Date])

1.PNG

Create below measure:

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

Result:

2.PNG

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

 

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.

View solution in original post

Highlighted

Thanks @v-danhe-msft 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors