Adding Condition to SWITCH function for Call Center

[jabueg]

Hi! I have a measure that calculates Total Calls, depending if an Advisor or Date is selected in a slicer.
It will calculate SUM of Total Calls using an inactive relationship between the two tables.

UpdatedProd521 = sales table
Combined Call Logs 5/17 = call logs table 

Inactive relationships between [Date] And [Advisor] columns

I need help to add a new argument that outputs SUM total from the Updated Call Logs 5/17[Calls] column if a single Advisor AND single Date are selected using the relationships between the two tables.

Here is the syntax I used that currently works if either Advisor or Date are selected:

Total Calls Dynamic =

    SWITCH(

        HASONEVALUE(UpdatedProd521[Advisor]),TRUE(),                

            CALCULATE(

                SUM('Combined Call Logs 5/17'[CALLS]),USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])  //When an advisor is selected

            )

         ,NOT HASONEVALUE(UpdatedProd521[Advisor]) && (NOT ISBLANK(MAX('UpdatedProd521'[Date]) || NOT ISBLANK(MAX('Combined Call Logs 5/17'[DATE])))),TRUE(),          

          CALCULATE(SUM('Combined Call Logs 5/17'[CALLS]),

                USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date])  //When date is selected 

            )

[v-veshwara-msft · Accepted solution]

Hi @jabueg ,

Thanks for reaching out to the Microsoft Fabric Community.

To achieve the requirement of calculating total calls based on Advisor and Date selections from the UpdatedProd521 table, I tested the scenario using a sample dataset with inactive relationships between the tables. Here’s the DAX measure that produces the expected result when either or both filters are applied:

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 are some screenshots for reference:

Also thanks to @DataNinja777  and @Jai-Rathinavel  for their insights and suggestions.

Attaching a PBIX file with the sample data and measure for testing.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider giving it a kudos and accepting it as the solution to help other members find it more quickly.

Thank you.

[jabueg]

Thank you @v-veshwara-msft This worked beautifully, and now I can see total calls for single dates and single advisor. 
I do need another Switch for an "Average Call Time" and "Average Hold Time" measures using the same inactive relationships and same type of conditions as the "Total Calls Dynamic" that you solved. Can you help with that as well?

Here is my current DAX for Average 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"

Average 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"

[DataNinja777]

Hi @jabueg ,

Before jumping into the fix, let’s call out the elephant in the model: this current setup isn’t scalable. The table name 'Combined Call Logs 5/17' alone is a red flag—it’s tied to a specific date, suggesting it was meant for a one-off analysis or a manually refreshed file. That doesn’t belong in a long-term Power BI model. Plus, relying on USERELATIONSHIP to toggle between inactive relationships based on slicer selections becomes fragile fast. If you ever need to scale this to handle more filters—like region, call type, or multiple date roles—it’ll get messy and unmaintainable. A more robust model would involve normalized dimension tables (e.g., a proper DimAdvisor and DateTable) and consistently active relationships, instead of writing increasingly complex measures to paper over modeling gaps.

Now, to answer the original question: you need to modify the existing SWITCH logic to handle the case where both a single Advisor and a single Date are selected. Here’s the revised measure:

Total Calls Dynamic =
SWITCH(
    TRUE(),
    HASONEVALUE(UpdatedProd521[Advisor]) && NOT ISBLANK(MAX(UpdatedProd521[Date])),
        CALCULATE(
            SUM('Combined Call Logs 5/17'[CALLS]),
            USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]),
            USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date])
        ),
    HASONEVALUE(UpdatedProd521[Advisor]),
        CALCULATE(
            SUM('Combined Call Logs 5/17'[CALLS]),
            USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])
        ),
    NOT HASONEVALUE(UpdatedProd521[Advisor]) && (NOT ISBLANK(MAX('UpdatedProd521'[Date])) || NOT ISBLANK(MAX('Combined Call Logs 5/17'[DATE]))),
        CALCULATE(
            SUM('Combined Call Logs 5/17'[CALLS]),
            USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date])
        )
)

The key addition here is the top condition that checks for both HASONEVALUE(Advisor) and a selected Date. This activates both relationships so the total is filtered correctly when both slicers are used. It’s placed first so it takes precedence before falling through to the other conditions. This patch solves the immediate issue, but long term, you’ll want to refactor the model to avoid having to micromanage relationships this way.

Best regards,

[jabueg]

Thanks for your response @DataNinja777. However, this did not solve the problem. It gave me this error:

What I need is to pull the SUM total of calls for a specific date from the Call Logs [Calls] column when a Advisor and Date is selected from a slicer from the Prod Table. There is currently active relationship between Case Name. 

And yes, I do plan on creating more solid dimension tables later on. This is more of a test analysis using smaller data sets.

[Jai-Rathinavel]

Hi @jabueg  Try out the below DAX

Total Calls Dynamic =
SWITCH(
    TRUE(),

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

    CALCULATE(
        SUM('Combined Call Logs 5/17'[CALLS]),
        USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]),
        USERELATIONSHIP('Combined Call Logs 5/17'[DATE], 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
    NOT ISBLANK(MAX(UpdatedProd521[Date])),

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

Thanks,

Jai Rathinavel | LinkedIn

[jabueg]

Thank you @Jai-Rathinavel for your response! Unfortunately this DAX resulted in an error for me.