Adding SWITCH for Average Call Time and Average Hold Time

[jabueg]

Hi! I need help adding SWITCH function to two separate DAX measures for a Call Center that calculates "Average Call Time" and "Average Hold Time. There are inactive relationships between Advisor and Date from a Prod Sales Table and Combined Call Logs Table. 

Please use the same slicer conditions I used in a previous Total Calls measure:

Total Calls Dynamic = 
SWITCH(
    TRUE(),

    // Case 1: Single Advisor AND Single Date selected
    HASONEVALUE(UpdatedProd521[Advisor]) &&
    HASONEVALUE(UpdatedProd521[Date]),

    CALCULATE(
        SUM('Combined Call Logs 5/17'[CALLS]),
        USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]),
        FILTER(
            'Combined Call Logs 5/17',
            'Combined Call Logs 5/17'[DATE] = VALUES(UpdatedProd521[Date])
        )
    ),

    // Case 2: Only Advisor is selected
    HASONEVALUE(UpdatedProd521[Advisor]),

    CALCULATE(
        SUM('Combined Call Logs 5/17'[CALLS]),
        USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])
    ),

    // Case 3: Only Date is selected
    HASONEVALUE(UpdatedProd521[Date]),

    CALCULATE(
        SUM('Combined Call Logs 5/17'[CALLS]),
        USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date])
    ),

    // Default: sum all calls if no or multiple selections
    SUM('Combined Call Logs 5/17'[CALLS])

Here is my DAX for average call time (without Switch) and outputs 0 if no call time

AverageCallDuration =

VAR TotalSeconds =

COALESCE(CALCULATE(AVERAGE('Combined Call Logs 5/17'[CALL TIME (SEC)]),USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])),0) //Replace YourTable and SecondsColumn

VAR Minutes =

    INT (TotalSeconds / 60)

VAR Seconds =

    MOD (TotalSeconds, 60)

RETURN

FORMAT (Minutes, "0") & " min"

Here's my DAX for Average Hold Time (without Switch) and outputs 0 if no hold time

Avg Hold Time =

VAR TotalSeconds =

    COALESCE(CALCULATE(AVERAGE('Combined Call Logs 5/17'[HOLD TIME (SEC)]),USERELATIONSHIP('UpdatedProd521'[Advisor], 'Combined Call Logs 5/17'[ADVISOR])),0) // Replace YourTable and SecondsColumn

VAR Minutes =

    INT ( TotalSeconds / 60 )

VAR Seconds =

    MOD ( TotalSeconds, 60 )

RETURN

    FORMAT ( Minutes, "0" ) & " min, " & FORMAT ( Seconds, "0" ) & " sec"

[Poojara_D12 · Accepted solution]

Hi @jabueg 

Updated AverageCallDuration with SWITCH:

AverageCallDuration =
VAR TotalSeconds =
    SWITCH(
        TRUE(),

        // Case 1: Single Advisor AND Single Date selected
        HASONEVALUE(UpdatedProd521[Advisor]) && HASONEVALUE(UpdatedProd521[Date]),
        CALCULATE(
            AVERAGE('Combined Call Logs 5/17'[CALL TIME (SEC)]),
            USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]),
            FILTER(
                'Combined Call Logs 5/17',
                'Combined Call Logs 5/17'[DATE] = VALUES(UpdatedProd521[Date])
            )
        ),

        // Case 2: Only Advisor is selected
        HASONEVALUE(UpdatedProd521[Advisor]),
        CALCULATE(
            AVERAGE('Combined Call Logs 5/17'[CALL TIME (SEC)]),
            USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])
        ),

        // Case 3: Only Date is selected
        HASONEVALUE(UpdatedProd521[Date]),
        CALCULATE(
            AVERAGE('Combined Call Logs 5/17'[CALL TIME (SEC)]),
            USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date])
        ),

        // Default: average over all call times if no or multiple selections
        AVERAGE('Combined Call Logs 5/17'[CALL TIME (SEC)])
    )

VAR Minutes = INT(TotalSeconds / 60)
VAR Seconds = MOD(TotalSeconds, 60)

RETURN
    IF(TotalSeconds = 0,
       "0 min",
       FORMAT(Minutes, "0") & " min, " & FORMAT(Seconds, "0") & " sec"
    )

Updated Avg Hold Time with SWITCH:

AvgHoldTime =
VAR TotalSeconds =
    SWITCH(
        TRUE(),

        // Case 1: Single Advisor AND Single Date selected
        HASONEVALUE(UpdatedProd521[Advisor]) && HASONEVALUE(UpdatedProd521[Date]),
        CALCULATE(
            AVERAGE('Combined Call Logs 5/17'[HOLD TIME (SEC)]),
            USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]),
            FILTER(
                'Combined Call Logs 5/17',
                'Combined Call Logs 5/17'[DATE] = VALUES(UpdatedProd521[Date])
            )
        ),

        // Case 2: Only Advisor is selected
        HASONEVALUE(UpdatedProd521[Advisor]),
        CALCULATE(
            AVERAGE('Combined Call Logs 5/17'[HOLD TIME (SEC)]),
            USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])
        ),

        // Case 3: Only Date is selected
        HASONEVALUE(UpdatedProd521[Date]),
        CALCULATE(
            AVERAGE('Combined Call Logs 5/17'[HOLD TIME (SEC)]),
            USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date])
        ),

        // Default: average over all hold times if no or multiple selections
        AVERAGE('Combined Call Logs 5/17'[HOLD TIME (SEC)])
    )

VAR Minutes = INT(TotalSeconds / 60)
VAR Seconds = MOD(TotalSeconds, 60)

RETURN
    IF(TotalSeconds = 0,
       "0 min",
       FORMAT(Minutes, "0") & " min, " & FORMAT(Seconds, "0") & " sec"
    )

• The SWITCH(TRUE()) block checks slicer selections on Advisor and Date to decide which relationship or filter to apply.
• USERELATIONSHIP activates the inactive relationship for filtering.
• When both Advisor and Date are selected, it filters for that date as well.
• Default case averages over all call or hold times without filter.
• The result formats seconds into "minutes and seconds" or shows "0 min" if zero.
•  

[jabueg]

Thank you!!! This worked perfectly.