Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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?
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
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |