Forum Discussion

jabueg's avatar
jabueg
Helper I
1 year ago
Solved

Adding Condition to SWITCH function for Call Center

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....
"DAX " "DAX help" 'Need help'
"FILTER"
"SWITCH"
DAX SWITCH
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

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

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's avatar
Jai-Rathinavel
Super User
1 year ago

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

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