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.
My datamodel:
Location Table (Location Code, Location Description)
Personnel Table (Emp_Num, Location Code)
Incidents Table (Emp Num, Location Code, Incident Type)
Active Relationships are Location Table Location Code to Personnel Table Location Code and Personnel Table Emp Num to Incidents Table Emp Num.
I have Measures that are counts of incidents of a given type. For example:
DUI Crash Incident = CALCULATE(DISTINCTCOUNT(IncidentTable[Incident_ID]), IncidentTable[IncidentType_ID] = "DUI Crash") + 0
I have a Date Range slicer on my report page, and a Location Slicer.
When a User picks from the Location Slicer, I want to show a List of the Employees in the Selected Location, along with their counts of the types of incidents they each handled for a selected time period (so for example if I pick Location A - I want to see all the employees at Location A in Column 1 of the Matrix, Count of each person's DUI Crash Incidents in Column 2, Count of each person's Seatbelt Incidents in Column 3, etc.
I put a matrix on my report screen. I put Employee Name in the rows field, and this works when I change Location Selection with the slicer (for Example, I select Location A and the Matrix list shows me only the Employees in Location A). So far so good.
However, when I add a Measure (For example, the DUI Crash Incidents Measure above), the matrix shows ALL employees with their DUI Crash Incidents counts, not just the ones in Location A.
I thought if, filter-wise, I'm going from Location Table to Employee Table to Incident Table (which is how my model looks), then if I'm filtering by Location, the active relationships between the tables would be how the Measures are filtered automatically. But instead, I'm getting ALL employees with their DUI Crash Incidents counts, not just the ones in the selected Location that I need.
How can I fix this?
Thanks for any help!
Solved! Go to Solution.
I actually got it to work by adding a filter to the visual that says "First Location Code is not blank". It seems when I pulled the location code field into the visual, the only location code that showed up was the selected location code - location code field for all the other employees in the list was blank. So I just used the 'is not blank' filter and that works.
Could be a number of things like cardinality of your relationships. Any chance you could create a sample file or post sample data where this could be recreated? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
I actually got it to work by adding a filter to the visual that says "First Location Code is not blank". It seems when I pulled the location code field into the visual, the only location code that showed up was the selected location code - location code field for all the other employees in the list was blank. So I just used the 'is not blank' filter and that works.
Correction - the matrix shows every employee, but only counts for the employees who are in the selected location.
My question is still, how to get only the employees within the selected location to show on the list. I can't filter this by making the Measure count > 0 because some peoople in the selected location might have a count of 0 and the users want to see all employees in the selected location, whether their counts are 0 or higher.
Thanks!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |