DISTINCT COUNT and GROUP BY

[Anonymous]

Hello

I have the data:

A	1	01/01/2020
B	2	02/01/2020
C	3	03/02/2020
E	4	04/02/2020
D	5	05/03/2020
D	6	06/03/2020

How can I calculate the distinct number of Col1, where Col2 is >2 and group the results by month?

So the result should be:

Jan=0

Feb=2

Mar=1

Thanks!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Month = MONTH('Table'[Date])

Month_mmm = FORMAT([Date],"mmm")

2. Create measure.

Measure =
var _2=CALCULATE(DISTINCTCOUNT('Table'[Column1]),FILTER('Table','Table'[Column2]>2&&'Table'[Month]=MAX('Table'[Month])))
return IF(_2=BLANK(),0,_2)

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

you can create a column

month = FORMAT('Table'[Column3],"mmm")

then create a measure

Measure = CALCULATE(DISTINCTCOUNT('Table'[Column1]),FILTER('Table','Table'[Column2]>2))+0

[amitchandak]

@Anonymous , Create a measure like this

calculate(distinctcount(Table[col1]), filter(Table, Table[col2] >2))+0

[Anonymous]

@amitchandak 

Thanks but how do I group by Month and what is the '+0' part please?