Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm creating an exportable page for our technicians at work, the report will show their statistics vs. the averages for the regions they're in. I'll select a date range and then the technician by name in slicers. All my other region average metrics are working except this one to show tickets that were closed older than 60 days, and the problem is with the FILTER.
RegionAgedTicketCount =
VAR
Region = SELECTEDVALUE(Technicians[TechRegion])
RETURN
CALCULATE(
COUNT(sndata[Call]),
ALLEXCEPT(sndata, 'Calendar'),
Technicians[TechRegion] = Region,
FILTER(sndata, DATEDIFF(sndata[OpenedDate], COALESCE(sndata[ResolvedDate], TODAY()), DAY) >= 60)
)
Instead of returning the expected region average at some point the slicer for the individual technician is being reapplied and it's only returning the number of aged tickets for the individual technician. If I remove the line with the date FILTER so it's essentially just returning a count of all the calls closed by the region, then it works as expected and returns the entire region's count rather than just the individual technicians.
I'm not sure why the FILTER statement reapplies the specific technician from the slicer, but does anyone have any suggestions on how to prevent this from happening?
Solved! Go to Solution.
Sorted it out;
RegionAgedTicketCount =
VAR
Region = SELECTEDVALUE(Technicians[TechRegion])
RETURN
CALCULATE(
COUNT(sndata[Call]),
Technicians[TechRegion] = Region,
FILTER(ALLEXCEPT(sndata, 'Calendar'), DATEDIFF(sndata[OpenedDate], COALESCE(sndata[ResolvedDate], TODAY()), DAY) >= 60)
)
That seems to work. I assumed CALCULATE would apply the filters sequentially but that seems to not be the case as this works even with the Technicians[TechRegion] = Region in the sequence ahead of the FILTER. Ended up being a simple solution I guess.
Morning,
I'm still struggling a little bit here, since FILTER seems to be the issue is there any other way to replicate this type of filtering in my Measure?
FILTER(sndata, DATEDIFF(sndata[OpenedDate], COALESCE(sndata[ResolvedDate], TODAY()), DAY) >= 60)
Attaching this to hopefully illustrate the problem. The visual has a filter applied to it limiting it to one single technician. In the first example Regional Average 1 you can see that it's able to correctly calculate the regional total. In the second example as soon as the FILTER line is added in the filter on the visual for the single technician is being re-applied and it's not calculating the correct regional average.
Sorted it out;
RegionAgedTicketCount =
VAR
Region = SELECTEDVALUE(Technicians[TechRegion])
RETURN
CALCULATE(
COUNT(sndata[Call]),
Technicians[TechRegion] = Region,
FILTER(ALLEXCEPT(sndata, 'Calendar'), DATEDIFF(sndata[OpenedDate], COALESCE(sndata[ResolvedDate], TODAY()), DAY) >= 60)
)
That seems to work. I assumed CALCULATE would apply the filters sequentially but that seems to not be the case as this works even with the Technicians[TechRegion] = Region in the sequence ahead of the FILTER. Ended up being a simple solution I guess.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
99 | |
97 | |
73 | |
72 |