Forum Discussion

MorganBauwens's avatar
MorganBauwens
New Member
3 years ago
Solved

Count multi-callers in multiple services

Hello all,

 

I'd love to get some help with DAX.

 

Here is the context : 

We have a hotline with multiple call-centers. All the events are recorded in a PowerBI Dataset. Here are the main columns that I focus on :

Table : ServiceTask

FieldMeaningExample
InitialSipFromCustomer's phone number[email protected]
InitialSipToCall-center choosed by customer[email protected]
StartedDateIDDate (integer)20221231
TaskResultIDResult of the call (handled, not handled...)5

 

My goal is to create a measure with DAX that will count, for each day, the number of customers that called us more than one time and choosed at least two different contact centers ("shoppers"), and having a task result of 1 or 5.

So : 

Customer 1 called us twice, but chosed the same contact center in both occasion => do not count

Customer 2 called us twice, chosed call-center 1 the first time, and call-center 4 the second time => count

 

My final goal is to create a visual (table) a bit like this : 

StartedDateIDCount InitialSipFromCount Shoppers
2023010125641201
2023010228451540
2023010335021745

 

Being connected live to the dataset, I am not able to create or modify any column or field.

Is there anything I can do ?

 

Thanks a lot for your time and help ! 🙂 

  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Cust called twice =
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( 'Service Task'[Initial SIP from] ),
        "@num call centres", CALCULATE ( DISTINCTCOUNT ( 'Service Task'[Initial SIP to] ) )
    )
VAR Result =
    COUNTROWS ( FILTER ( SummaryTable, [@num call centres] >= 2 ) )
RETURN
    Result

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Cust called twice =
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( 'Service Task'[Initial SIP from] ),
        "@num call centres", CALCULATE ( DISTINCTCOUNT ( 'Service Task'[Initial SIP to] ) )
    )
VAR Result =
    COUNTROWS ( FILTER ( SummaryTable, [@num call centres] >= 2 ) )
RETURN
    Result