Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm having troubling in getting MINX and MAXX to give the correct result when the filter context is altered by a slicer. The data relates to a call centre. I'm trying to find out which agent managed the most and least calls per hour.
It works properly when showing how many calls each handled for both inbound and outbound calls. But when sliced for InBound or Outbound, it gives the wrong response.
The dataset contains both the inbound and outbound calls (one row for each call), which agent made (or received) the call, and the hour that the call took place.
Here is the code to find the MINX, but MAXX is almost exactly the same. It works by finding counting how many hours an agent managed a call in (calculated through Group By, and stored in HoursAvailable), and how calls an agent took, which is a simple row count. I have using ALL twice, once to ensure that the HoursAvailable uses the hours used for inbound and outbound (even if the slicer only selects one), and another to ensure that MINX is calculated by looking at every Agent's calls per hour.
Calls Per Hour Min1 =
CALCULATE (
MINX (
SUMMARIZE (
AllCalls,
AllCalls[AgentName],
"MinxCalls",
VAR HoursAvailable =
CALCULATE (
COUNTROWS (
GROUPBY ( AllCalls, Agents[AgentName], AllCalls[Hour], AllCalls[Date] )
),
ALL(AllCalls[Source])
)
VAR CallsTaken =
COUNTROWS ( AllCalls )
RETURN
DIVIDE ( CallsTaken, HoursAvailable )
),
[MinxCalls]
),
ALL(Agents[Agent, CC & TL])
)
When the slicer is selecting both inbound and outbound calls, the code correctly reports the minimum and maximum calls per hour:
But when slicing for inbound calls, the wrong minimums and maximums are reported:
Any help would be appreciated.
Thanks!
Solved! Go to Solution.
Hi @vpatel55 ,
Change your two measures, "Calls Per Hour Max" and "Calls Per Hour Min", like so:
Calls Per Hour Max 2 = MAXX(ALLSELECTED(AllCalls[Agent, CC, & TL]),[Calls Per Hour])
Calls Per Hour Min 2 = MINX ( ALLSELECTED ( AllCalls[Agent, CC, & TL] ), [Calls Per Hour] )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @vpatel55 ,
Change your two measures, "Calls Per Hour Max" and "Calls Per Hour Min", like so:
Calls Per Hour Max 2 = MAXX(ALLSELECTED(AllCalls[Agent, CC, & TL]),[Calls Per Hour])
Calls Per Hour Min 2 = MINX ( ALLSELECTED ( AllCalls[Agent, CC, & TL] ), [Calls Per Hour] )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Icey Perfect, that works! Your solution is so simple and elegant. Huge thanks to you!
Hi @vpatel55 ,
What about the expression below? I replaced ALL with ALLSELECTED. For their difference, please refer to this video: The Main Difference Between ALL And ALLSELECTED - Power BI & DAX.
Calls Per Hour Min1 =
CALCULATE (
MINX (
SUMMARIZE (
AllCalls,
AllCalls[AgentName],
"MinxCalls",
VAR HoursAvailable =
CALCULATE (
COUNTROWS (
GROUPBY ( AllCalls, Agents[AgentName], AllCalls[Hour], AllCalls[Date] )
),
ALLSELECTED ( AllCalls[Source] )
)
VAR CallsTaken =
COUNTROWS ( AllCalls )
RETURN
DIVIDE ( CallsTaken, HoursAvailable )
),
[MinxCalls]
),
ALLSELECTED ( Agents[Agent, CC & TL] )
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks very much @Icey but I'm afraid that ALLSELECTED is giving exactly the same response as ALL.
Any other thoughts or areas to check would be very much appreciated.
Thanks.
Hi @vpatel55 ,
If you don't mind, please share me a dummy PBIX just with necessary data, not real data, and remove sensitive information. I will conduct specific tests.
Best Regards,
Icey
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |