Forum Discussion
BeckiB
2 years agoFrequent Visitor
Add New Dynamically Updating Column To Data
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...
ERD
2 years agoCommunity Champion
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
BeckiB
2 years agoFrequent Visitor
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! 😊