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

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks v-danhe-msft