How to filter a report page in Power BI with a measure?

[LauraG8]

This is my first attempt at writing more complex DAX measures.

I have table with incidents by student. An incident can have multiple students and a student can have multiple incidents.
I have the below measure to calculate the number of student that have 2 - 6 incidents for the selected period.

Tier 2 :=
VAR IncidentByStudentCount =
SUMMARIZE (
FILTER (
IncidentInvolvement,
AND (
IncidentInvolvement[IncidentInvolvementType] = "Involved",
IncidentInvolvement[IncidentCategory] = "Major Negative"
)
),
IncidentInvolvement[SchoolCode],
IncidentInvolvement[StudentIdentifier],
"CountInvolvement", COUNT ( IncidentInvolvement[IncidentCode] )
)
VAR Tier2 =
FILTER (
IncidentByStudentCount,
AND ( [CountInvolvement] >= 2, [CountInvolvement] <= 6 )
)
RETURN
COUNTROWS ( Tier2 ) + 0

Now I want to filter my report page and only show data for the students that have between 2 to 6 incidents for the selected period. What would be the best way to do this?

I tried to solve it with the below measure.

Tier 2 Negative Incidents :=

VAR IncidentByStudentCount =

SUMMARIZE(

FILTER(

IncidentInvolvement,

AND (IncidentInvolvement[IncidentInvolvementType] = "Involved",

IncidentInvolvement[IncidentCategory] = "Major Negative")

),

IncidentInvolvement[SchoolCode],

IncidentInvolvement[StudentIdentifier],

"CountInvolvement"

COUNT(IncidentInvolvement[IncidentCode])

)VAR Tier2Table =

FILTER(

IncidentByStudentCount,

AND ([CountInvolvement] >= 2,

[CountInvolvement] <= 6)

)

VAR Tier2TableFilter =

CALCULATETABLE(

VALUES(IncidentInvolvement[StudentIdentifier]),

Tier2Table,

ALLSELECTED()

)

VAR Tier2NegativeIncidents =

CALCULATE(

COUNT(IncidentInvolvement[IncidentIdentifier]),

AND( IncidentInvolvement[IncidentInvolvementType] = "Involved",

IncidentInvolvement[IncidentCategory] = "Major Negative"

),

Tier2TableFilter)

RETURN

Tier2NegativeIncidents

This works when I have a visual by student. However, for my other visuals where I want to show the number of incidents by location etc. it doesn't work, because it will only show locations for those students where their incident location has between 2-6 incidents. So it executes the filter "AND ([CountInvolvement] >= 2,[CountInvolvement] <= 6)" over the location as well. Clearly I am doing something wrong. I hope someone can help me out!

@dax

[Anonymous]

Hi @LauraG8 ,

Did you want to use a measure as the page-level filter or use a measure to return the filtered results?

Is it possible to provide some dummy data and expected results?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LauraG8]

Hi Stephen, Thanks for reaching out!

Please see the dummy data below;

IncidentID	Student	Category	Behaviour	Location	Period	Academic year
100	Billy	Major	Bullying	Playground	Lunch Break	6
100	Dylan	Major	Bullying	Playground	Lunch Break	7
100	Kate	Major	Bullying	Playground	Lunch Break	7
110	Dylan	Major	Non-Compliance	Classroom	Morning session	7
150	Dylan	Minor	Inappropriate language	Classroom	Afternoon session	7
155	Dylan	Major	Physical Contact	Playground	Recess	7
155	Billy	Major	Physical Contact	Playground	Recess	6
225	Susan	Major	Physical Contact	Playground	Afternoon session	8
225	Dylan	Major	Physical Contact	Playground	Afternoon session	7

I have a page with the following (dummy) visuals;

I also have a card visual with the Tier 2 measure that calcualates the number of students that have between 2 - 6 Major incidents for the selected time frame (Year, term, week). In the dummy data that would be 2 (Dylan with 4 and Billy with 2). Now I would like to have a filter option (button) to only show data for the major incidents for students that have between 2-6 major incidents. In the dummy data that would be Dylan and Billy, with 6 major incidents. Behaviour visual would show 2 bullying, 1 Non-Compliance, 3 Physical contact. Location visual: 5 Playground, 1 Classroom. Period visual: 2 Lunchbreak, 1 Morning Session, 2 Recess and 1 Afternoon session.

I hope this makes sense!

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Download my PBI file from here.

Hope this helps.

[LauraG8]

Hi Ashish,

Thank you for provideing a solution. However, due to security restrictions I can't download your file. Can you please explain the solution/ write it out? Did you use a measure and if so can you provide me with the DAX code? Thanks!

[Ashish_Mathur]

There are way too many steps to write down.  Please use another connection to download the file.

[LauraG8]

Hi Ashish,

I have downloaded the file and had a bit of a play with it.

What I want to achieve in my report is slice by bracket. So when I click on the 2-6 bracket I just want to see data for Billy and Dylan. I don't seem to be able to do that. I hope you can help me out with this. Thank you! 🙂

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[LauraG8]

Hi Ashish,

Thank you for your very swift reply! 🙂

That works a bit better. However, when I add behaviour to the table and select 2-6 incidents I only see Dylan with 2 incidents for Physical contact. (see below)

I would like to see all data for both Billy and Dylan as per below example.

I hope that makes sense.

Thanks,

Laura

[Ashish_Mathur]

Hi,

This is the closest i can get it to.  Download the PBI file from here.

[LauraG8]

Thanks so much Ashish! 🙂👍

[amitchandak]

@LauraG8 , You need to segmentation for that

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Assume you have measure

Bucket/Segment code
measure
Measure = Count(Table[productid]) /

Create new Table
bucket = Generateseries(1,100,1)

new Measure
Countx(filter(Values(Table[user]), [Measure ] = max(bucket[Value])), [User])

between bucket

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[LauraG8]

Thanks for reaching out and sending your solution. Unfortunately, it's not the solution I was looking for in this instance.

Please see my further explantion on what I would like to achieve in my reply to Stephen Tao. Hope that explains it a bit better!