Count multi-callers in multiple services

[MorganBauwens]

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

Field	Meaning	Example
InitialSipFrom	Customer's phone number	+330636656565@voiipdomain.be
InitialSipTo	Call-center choosed by customer	call_center1@ourdomain.be
StartedDateID	Date (integer)	20221231
TaskResultID	Result 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 : 

StartedDateID	Count InitialSipFrom	Count Shoppers
20230101	2564	1201
20230102	2845	1540
20230103	3502	1745

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 · Accepted solution]

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

[MorganBauwens]

That is exactly what I needed, @johnt75 !
Thanks a lot for your help !