Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vpatel55
Kudo Commander
Kudo Commander

MINX and MAXX giving incorrect response when Filter Context is altered

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:

 

Working.png

 

But when slicing for inbound calls, the wrong minimums and maximums are reported:

 

Not working.png

 

Any help would be appreciated.

 

Thanks!

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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] )

 

in max 1.PNGin min 1.PNGout max 1.PNGout min 1.PNG

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

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] )

 

in max 1.PNGin min 1.PNGout max 1.PNGout min 1.PNG

 

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!

Icey
Community Support
Community Support

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.

Icey
Community Support
Community Support

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.