Measure based on grouped calculation without summarize

[kzorina]

I need to calculate measure based on groups. And tricky part is that I cannot use summarize as I need to have date filter and measure should dynamically change when I change a filter.

For example for data:

day	PurchaseId	UserId
1	1	1
1	2	1
1	3	2
1	4	2
1	5	3
2	6	1
2	7	2
2	8	3
2	9	4
3	10	1
3	11	4
3	12	4
3	13	4
3	14	4
4	15	1
4	16	2
4	17	3

When filter is all days - i want to see

UserId	Amount of purchases
1	5
2	4
3	3
4	5

And when filter is 3-4 day

UserId	Amount of purchases
1	2
2	1
3	1
4	4

EDITED

Moreover I want to have buckets afterwards, based on this amount (for exmple, 1-2 purchases, 3-5 purcahes) and operate with them

Bucket	Amount of users
1-2 purchases	0
3-5 purcahes	4

filter 3-4 day

Bucket	Amount of users
1-2 purchases	3
3-5 purcahes	1

Any ideas, how can I do this?

[v-lili6-msft]

hi, @kzorina 

You could try this way as below:

Step1:

You need to define buckets table.

Step2:

Use this formula to create the measure

Measure = var _amount= CALCULATE(COUNTA('Table'[PurchaseId])) return
CALCULATE(MAX(buckets[Tag]),FILTER(buckets,_amount>=buckets[From]&&_amount<=buckets[To]))

Result:

Best Regards,

Lin

[kzorina]

Thanks, @v-lili6-msft for a quick answer

Can I somehow achive it in a way so I can operate with bucket as fixed value. for example, calculate amount of users in some bucket?

[v-lili6-msft · Accepted solution]

hi, @kzorina 

You could use this formula to add a new measure

Measure 2 = var _table=SUMMARIZE(GENERATE(buckets,'Table'), [Tag],[From],[To],[UserId],"Amount of users",CALCULATE(COUNTA('Table'[PurchaseId]))) return
COUNTAX(FILTER(_table,[Amount of users]>=[From]&&[Amount of users]<=[To]),[UserId])

Then drag Tag field from buckets table and the measure into a visual

Best Regards,

Lin

[kzorina]

@v-lili6-msft Thanks a lot! That worked!