Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables Case and Task.
When a customer calls the agent logs a Case and the phone system attaches a Task to that Case. Should a customer call back about the same thing then the agent should open the Case back up and the phone system would attach a second task to that Case. However, because of interpretation the agent may open a new Case as opposed to reopening the Case.
I want to calculate repeat call but cannot rely on the Task table alone as the agent may not reopen the Case; they may create a new one. I cannot rely on the Case table alone because that would not account for when the Case is reopened. Therefore I need to use a combination of the two.
The definition of a repeat call rate is where the following parameters match AccountId, Product and SubCase within two days of each other based on the Created Date.
I have already done this via Power Query and some DAX columns and measures. However this requires merging the Task table with the Case table in Power Query resulting in a longer refresh time and some duplication of data, therefore increasing model size.
I want to know if what I am currently doing is achievable with DAX by just loading the Case and Task tables without merging in Power Query with the aim of improving refresh times and model size.
Below is a link to a PBIX file demoing the model and my calculations so far.
https://1drv.ms/u/s!AkFN9EElJAPfkAAJvN5DZ3XKs0v2?e=T1ysGQ
Any help is much appreciated
It can be achieved with DAX in the following way:
1) chane the relationship between Task and Case like this:
2) add the following columns to task:
AccountId = RELATED('Case'[AccountId])
Task = RELATED('Case'[Product])
SubCat = RELATED('Case'[SubCat])
PreviousCall =
VAR __AccountId = 'Task'[AccountId]
VAR __Task = 'Task'[Task]
VAR __SubCat = 'Task'[SubCat]
VAR __Date = 'Task'[CreatedDate]
VAR __CasesBeforeCurrent =
FILTER (
Task,
'Task'[AccountId] = __AccountId
&& 'Task'[Task] = __Task
&& 'Task'[SubCat] = __SubCat
&& 'Task'[CreatedDate] < __Date
)
RETURN
CALCULATE ( LASTNONBLANK ( 'Task'[CreatedDate], TRUE ), __CasesBeforeCurrent )
with that you have all the details needed for your measures to work
As for the performance - the refresh will definitely be faster, the model itself may be a bit slower, so I think the best way would be just to test it and compare
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |