group by in calculated measure

[jonasr]

Hi all,

I am trying to create a new measure which gives me the number of rows per person and breaks this into intervals. 

Example data/result:

Person | home_p | Count | Interval
xxx | 1 | 5 | A 1-5
xxx | 0 | 5 | A 1-5
xxx | 1 | 5 | A 1-5
xxx | 0 | 5 | A 1-5
xxx | 1 | 5 | A 1-5
yyy | 1 | 6 | A 6-15
yyy | 1 | 6 | A 6-15
yyy | 1 | 6 | A 6-15
yyy | 1 | 6 | A 6-15
yyy | 1 | 6 | A 6-15
yyy | 1 | 6 | A 6-15
aaa | 1 | 3 | A 1-5
aaa | 1 | 3 | A 1-5
aaa | 1 | 3 | A 1-5
bbb | 0 | 3 | A 1-5
bbb | 0 | 3 | A 1-5
bbb | 0 | 3 | A 1-5

I can accomplish this using summarize to create new table:

count = SUMMARIZE(score,score[Person],"Count",COUNT(score[home_p]))

Intervals = SWITCH(TRUE(),
             AND([Count]>=0, [Count]<=5), "A 1-5",
             AND([Count]>=6, [Count]<=15), "B 6-15",
             AND([Count]>=16, [Count]<=25), "C 16-25",
             AND([Count]>=26, [Count]<=35), "D 26-35",
             "E 35+"
           )

However, I need the resulting variable to be a measure. I want to use it in a Slicer to filter results of other visualisations (shown with respect to the Person variable), but the problem is that I have multiple other filters and so the count needs to change dynamically to reflect selection in other filters. 

Any idea how to approach this?

Thanks

Jonas 

[Eric_Zhang · Accepted solution]

---

However, I need the resulting variable to be a measure. I want to use it in a Slicer to filter results of other visualisations (shown with respect to the Person variable), but the problem is that I have multiple other filters and so the count needs to change dynamically to reflect selection in other filters.

---

@jonasr

A measure as below? It would vary interactively with other visuals. But you can't use measure in a Slicer, how about the Visual Level filter?

Intervales = 
VAR cnt = COUNT(Score[home_p])
RETURN
SWITCH(TRUE(),cnt<=5,"A 1-5",cnt<=15,"B 6-15",cnt<=25,"C 16-25",cnt<=35,"D 26-35","E 6-15")

[OwenAuger]

Hi Jonas,

Have a look at Dynamic Segmentation on DAX Patterns - it covers what you're trying to do here.

http://www.daxpatterns.com/dynamic-segmentation/

Reply back if you need more help on the table/measure setup.

Owen 🙂