Forum Discussion

jabueg's avatar
jabueg
Helper I
1 year ago
Solved

Adding SWITCH for Average Call Time and Average Hold Time

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"

 

"DAX " "DAX help" 'Need help'
"DAX SWITCH"
"SWITCH"
average measure
call center
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    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.
    •  

2 Replies

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    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's avatar
      jabueg
      Helper I
      1 year ago

      Thank you!!! This worked perfectly.