Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mark_carlisle
Advocate IV
Advocate IV

Repeat Caller (Done via Power Query but is there a more efficient way with DAX?)

I have two tables Case and Task.

 

  • Case holds all of the details of the Cases created by agents.
  • Task holds the call details from the telephony system. Task are created for every customer call.

 

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

1 REPLY 1
Stachu
Community Champion
Community Champion

It can be achieved with DAX in the following way:

1) chane the relationship between Task and Case like this:

Capture.PNG

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors