topic Re: Add New Dynamically Updating Column To Data in DAX Commands and Tips

Add New Dynamically Updating Column To Data

BeckiB — Fri, 15 Sep 2023 11:07:41 GMT

Hi,

I'm relatively new to using DAX in PowerBI and have turned to ChatGPT for some help but it's repeatedly giving me 2 solutions, neither of which work!

The Data:

I have just one data table of inbound calls into a call centre. Columns include: StartDateTime, Agent Name, Caller Number (among others).

Inbound calls can be answered by different agents, so a caller number may appear 20 times on 20 different datetime occasssions and the caller may have spoken to 5 different agents.

StartDateTime	AgentName	CallerNumber
02/09/2023 19:01	Bob	Caller01
02/09/2023 19:15	Alice	Caller01
07/09/2023 15:33	Alice	Caller02
13/09/2023 12:04	Bob	Caller01

I have created some calculated fields including:

MinCallStartDateTime (returns the lower value of the date filter applied to the dashboard).

MaxCallStartDateTime (returns the upper value of the date filter applied to the dashboard).

MaxCallsMade (returns count of caller number based on date filters applied to dashboard).

FirstCall (returns the datetime of the first call from the selected Caller Number based on the date filters applied to the dashboard).

For Caller01

MinCallStartDateTime	01/09/2023
MaxCallStartDateTime	15/09/2023
MaxCallsMade	3
FirstCall	02/09/2023 19:01

What I want to achieve:

1) I want to know which agent answered the FirstCall "FirstCallAgent" - my dashboard currently returns the FirstCall PER agent (for each Caller Number Selected), I just care about who was the first agent to answer the very first inbound call (dependent on date filters applied to the dashboard).

02/09/2023 19:01	Bob
~~02/09/2023 19:15~~	~~Alice~~ (this currently shows but I don't care about it)

If possible it would be ideal to create a new column called "CallTally" to the table which can be updated to count each inbound call from that number dynamically to update based upon any date filters applied to the dashboard.

StartDateTime	AgentName	CallerNumber	CallTally - may change if date filter changes
02/09/2023 19:01	Bob	Caller01	1
02/09/2023 19:15	Alice	Caller01	2
07/09/2023 15:33	Alice	Caller02	1
13/09/2023 12:04	Bob	Caller01	3

2) "RepeatCallPerc" - The goal is to find out which agent is best at resolving customer problems meaning there will be fewer repeat calls / MaxCallsMade from Caller Number where one agent answered the very first call, relative to if another agent answered that first call (since a call can be answered by any agent).

Bob	100% (100% of calls, where Bob answered the first call, called in at another time)(Caller01)
Alice	0% (0% of calls, where Alice answered the first call, called in at another time)(Caller02)

The best way I can think about doing this is to calculate: for each agent - SUM the total instances of CallTally= 1 (according to the new column created as part of step 1).

Then divide this by the SUM of the total MaxCallsMade for caller numbers whereby the agent answered the FirstCall (regardless of which agents answered future calls) - does that make sense?

Extra:

3) A third goal would be to isolate which agent answered the last call "LastCallAgent" from each caller number (meaning that agent was the one to resolve that query) - to be able to sum this up and see that calls handled by Agent X are more likely to be resolved (and therefore the customer is less likely to call in) compared to calls handled by Agent Y.

Expressing this as a percentage for comparison reasons would also be useful "LastCallAgentPerc" = Count LastCallAgent / TotalLastCalls = SUM("LastCallAgent"). But again this would require updating the above table /column tallying inbound calls within the filtered date range.

Bob	50% (50% calls Bob handled were "last calls" (Caller01)
Alice	50% (50% of calls Alice handled were "last calls" (Caller02)

Anyway, I have absolutely no idea how to begin this! Or if it's even possible.

I would appreciate any insight and expertise you may be able to add to this. Or if you can add an easier way to think about this.

Thanks.

Re: Add New Dynamically Updating Column To Data

Mahesh0016 — Fri, 15 Sep 2023 12:41:27 GMT

BeckiB Please Share your Dummy Data and expected output in table.

Re: Add New Dynamically Updating Column To Data

Mahesh0016 — Fri, 15 Sep 2023 13:19:23 GMT

BeckiB
> MinCallStartDateTime = MIN(usedinslicer[Column])

> MaxCallStartDateTime = MAX(usedinslicer[Column])
> MaxCallsMade = calculate(Count(Columnname),counttabledate[Column]>=MinCallStartDateTime&&counttabledate[Column]<=MaxCallStartDateTime)

Re: Add New Dynamically Updating Column To Data

BeckiB — Fri, 15 Sep 2023 13:59:56 GMT

Hi, thanks for your reply. As above these are the fields that I had already calculated.

The information below "What I want to achieve" outlines what I require help with. Everything above this is for context. Thanks.

Re: Add New Dynamically Updating Column To Data

ERD — Sat, 16 Sep 2023 15:41:33 GMT

BeckiB , you might have to play with the measures according to your model, but here are the ones I used:

MinCallStartDateTime = MIN ( 'Date'[Date] )

MaxCallStartDateTime = MAX ( 'Date'[Date] )

MaxCallsMade = VAR mn = [MinCallStartDateTime] VAR mx = [MaxCallStartDateTime] VAR tableWithinDates = CALCULATETABLE ( VALUES ( CallCent[CallerNumber] ), FILTER ( ALL ( CallCent ), CallCent[StartDateTime] >= mn && CallCent[StartDateTime] <= mx ) ) RETURN MAXX ( tableWithinDates, CALCULATE ( COUNT ( CallCent[CallerNumber] ) ) )

FirstCall = VAR mn = [MinCallStartDateTime] VAR mx = [MaxCallStartDateTime] RETURN CALCULATE ( MIN ( CallCent[StartDateTime] ), ALL( CallCent[StartDateTime] ), CallCent[StartDateTime] >= mn && CallCent[StartDateTime] <= mx )

FirstCallAgent = VAR firstCall = [FirstCall] VAR t = FILTER ( CallCent, CallCent[StartDateTime] = FirstCall ) RETURN MAXX ( t, [AgentName] )

CallTally = VAR mn = [MinCallStartDateTime] VAR mx = [MaxCallStartDateTime] RETURN CALCULATE ( COUNT ( CallCent[CallerNumber] ), ALL ( CallCent ), CallCent[StartDateTime] >= mn, CallCent[StartDateTime] <= mx, CallCent[StartDateTime] <= MAX ( CallCent[StartDateTime] ), VALUES ( CallCent[CallerNumber] ) )

RepeatCallPerc = VAR t = FILTER ( ADDCOLUMNS ( SUMMARIZE ( CallCent, CallCent[StartDateTime], CallCent[AgentName], CallCent[CallerNumber] ), "CallTally", [CallTally], "MaxCallsMade", [CallsPerCaller] ), [CallTally] = 1 && [CallTally] <> [MaxCallsMade] ) VAR rows_first = COUNTROWS ( FILTER ( CallCent, [CallTally] = 1 ) ) VAR rows_repeat = COUNTROWS ( t ) RETURN DIVIDE ( rows_repeat, rows_first, 0 )

LastCallAgent = VAR mn = [MinCallStartDateTime] VAR mx = [MaxCallStartDateTime] VAR caller = MAX ( CallCent[CallerNumber] ) VAR maxDate = CALCULATE ( MAX ( CallCent[StartDateTime] ), CallCent[CallerNumber] = caller, CallCent[StartDateTime] >= mn && CallCent[StartDateTime] <= mx ) RETURN CALCULATE ( MAX ( CallCent[AgentName] ), CallCent[StartDateTime] = maxDate )

LastCallAgentPerc = VAR mn = [MinCallStartDateTime] VAR mx = [MaxCallStartDateTime] VAR t = FILTER ( ADDCOLUMNS ( SUMMARIZE ( CallCent, CallCent[StartDateTime], CallCent[AgentName], CallCent[CallerNumber] ), "@maxDt", VAR lastDt = CALCULATE ( MAX ( CallCent[StartDateTime] ), ALL ( CallCent[StartDateTime], CallCent[AgentName] ) ) RETURN lastDt ), [StartDateTime] = [@maxDt] && [StartDateTime] >= mn && [StartDateTime] <= mx ) VAR allCallersCalls = CALCULATE ( COUNTROWS ( VALUES ( CallCent[CallerNumber] ) ), ALL ( CallCent ), CallCent[StartDateTime] >= mn && CallCent[StartDateTime] <= mx ) VAR currentAgentLastCalls = COUNTROWS ( t ) RETURN currentAgentLastCalls / allCallersCalls

Re: Add New Dynamically Updating Column To Data

BeckiB — Tue, 19 Sep 2023 08:11:19 GMT

Thank you so much for the detailed reply! I really appreciate it.

I have not yet had chance to test it, but once I do, I will get back to you and let you know! 😊