cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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:

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
Employee

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:

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.
Anonymous
Not applicable

Thanks @v-danhe-msft

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors