cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBIkalaimo Frequent Visitor
Frequent Visitor

Filtering and Measures based on a field related to slicer selection

I have an Incident fact table and an employee table with an employee number key joining it to the Incident Fact table.  Another field on both the employee table and fact table is Location Code.

Users want to compare how one employee's stats compare to others in the same location code.

So I have employee number in the slicer - when employee number is selected, I would need distinctcounts of rows from the fact table for this employee.  And then I would also need distinctcounts of rows from the fact table based on that employee's location code.

 

So:  Jane Miller at Location ABC123 had 10 Type Z incidents and 20 Type X incidents in the past 2 months

         ALL of Location ABC123 had 85 Type Z incidents and 239 Type X Incidents in the past 2 months

 

I can't figure out how to create Measures/filters for the Location Code piece. 

This measure gives me the correct location code of the selected person:

LocMeasure = SELECTEDVALUE(Personnel[LocCode])

 

Then I tried putting a Card visual on my report and using the below to calculate a count of incidents:

TestIncidentCountForLoc = var SelectedLoc = [LocMeasure] Return CALCULATE(DISTINCTCOUNT(Incidents[TypeX]), Incidents[LocCode] = SelectedLoc)

 

When I drop this measure into my card, if I Edit Interactions and filter on Employee slicer for the card, the card is only showing incidents for that employee (so in Jane's case, 20 Incidents instead of 239 for the entire ABC123 Location).  If I do not filter on Employee slicer, then I do not get a value for LocMeasure on that Card visual

(TestIncidentLoc = var SelectedLoc = [LocMeasure] Return SelectedLoc   Gives me a blank)

 

So I'm not sure what to do.  It seems like a simple request, but I can't figure out how to do it in Power BI.

Thanks for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filtering and Measures based on a field related to slicer selection

You're very close.

 

TestIncidentCountForLoc = 
var SelectedLoc = [LocMeasure]
RETURN
CALCULATE(DISTINCTCOUNT(Incidents[TypeX]), FILTER(ALL(Incidents), Incidents[LocCode] = SelectedLoc))

What was happening before was that the calculation was inheriting filters from your slicers by default.  So when you selected one person, then it only used that person's data where [LocCode] = SelectedLoc, so every entry.  By adding ALL, you remove those filters from slicers and get a value using the data from the entire table where LocCode = SelectedLoc.

2 REPLIES 2
Super User
Super User

Re: Filtering and Measures based on a field related to slicer selection

You're very close.

 

TestIncidentCountForLoc = 
var SelectedLoc = [LocMeasure]
RETURN
CALCULATE(DISTINCTCOUNT(Incidents[TypeX]), FILTER(ALL(Incidents), Incidents[LocCode] = SelectedLoc))

What was happening before was that the calculation was inheriting filters from your slicers by default.  So when you selected one person, then it only used that person's data where [LocCode] = SelectedLoc, so every entry.  By adding ALL, you remove those filters from slicers and get a value using the data from the entire table where LocCode = SelectedLoc.

Highlighted
PowerBIkalaimo Frequent Visitor
Frequent Visitor

Re: Filtering and Measures based on a field related to slicer selection

It works!  Thank you!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 288 members 3,254 guests
Please welcome our newest community members: