Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jonasr
Advocate I
Advocate I

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 | 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 

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
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")

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft Employee
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")
OwenAuger
Super User
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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors