Distinct Count per day and than sum of those per month

[lukaszbrdon]

Hi All,

How to create the measure that will firstly calculate distinct count per day and than will sum those result to month level.

as per example:

date	id
01/01/2021	a
01/01/2021	b
02/01/2021	a
02/01/2021	b
02/01/2021	c
03/01/2021	a
15/01/2021	a
15/01/2021	b

Needed measure:

Date	Measure Distinct Count per day	per month
01/01/2021	2	8
02/01/2021	3	8
03/01/2021	1	8
04/01/2021	2	8

Thank you in advance.

[lukaszbrdon]

Thank you, but it is not what i am looking for. It's a part of more complicated measure, so I need achieve that within the maesure.

the sum() can be used only for column, there is no possibility to create such Measure.

[truptis · Accepted solution]

@lukaszbrdon 
Try this:
m1 = var distinct_count_id = countrows(summarize(tablename,[date],[id]))
return 
sum(distinct_count_id)

this will return total sum of distinct values.

if you need it on a monthly level, use the date hierarchy thing and select month and year. this way, only month wise sum of distinct ids would show up.

Hope this helps you.
Regards,
TruptiS

[lukaszbrdon]

Thank you, but it is not what i am looking for. It's a part of more complicated measure, so I need achieve that within the maesure.

[truptis]

Hi @lukaszbrdon ,
You don't need a measure to have count per day. 
Take a table visual, Drag in Date and ID field. On the ID field, right click and see Count option. It will give you the count.
& for the total, keep the total on on the format pane. 

Also, for distinct count, you can select Distinct of Count option after the right click.

Hope this helps.