Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ! 🙂
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |