Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
It works! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |