cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## group by in calculated measure

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 | Intervalxxx | 1 | 5 | A 1-5xxx | 0 | 5 | A 1-5xxx | 1 | 5 | A 1-5xxx | 0 | 5 | A 1-5xxx | 1 | 5 | A 1-5yyy | 1 | 6 | A 6-15yyy | 1 | 6 | A 6-15yyy | 1 | 6 | A 6-15yyy | 1 | 6 | A 6-15yyy | 1 | 6 | A 6-15yyy | 1 | 6 | A 6-15aaa | 1 | 3 | A 1-5aaa | 1 | 3 | A 1-5aaa | 1 | 3 | A 1-5bbb | 0 | 3 | A 1-5bbb | 0 | 3 | A 1-5bbb | 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

1 ACCEPTED SOLUTION
Microsoft Employee

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")```
2 REPLIES 2
Microsoft Employee

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")```
Super User

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 🙂

Owen Auger
Blog

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.