Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Howdy. I've been beating my head against the wall for a few days now, and I'd love some help if possible.
I'm working with some call center data. The measure in question simply sums up the number of calls agents have answered since they were released from training.
Answered Since Release = CALCULATE( SUM('User Call Details'[ACD Calls Answered]),
FILTER(DatesTable,
DatesTable[Date]>=max('Training Classes'[Training Release Date])))
What I have currently works when I'm looking at a single class of agents, however there's a breakdown when viewing agents from multiple classes, as seen in the image below.
The values for [Answered Since Release] are correct for each row. I had hoped that the summary row would be the total of the two, but it ended up being the number of calls agents from both classes have answered since September 4, 2023. I can see where that's coming from, as I've indicated max(Training Release Date) in the DAX. What I can't wrap my head around is how to get that summary value to accurately account for the different training release dates of each group of agents. I've played around with variations of ALL* functions, KEEPFILTERS, and REMOVEFILTERS, but I haven't managed to stumble onto the right answer yet.
For good measure (😁), here's the relevant portion of the data model. Call data is in "User Call Details" and links to the "Call Center Roster" through "User IDs," which is a bridge table that helps condense users with multiple IDs in the system down to a single Employee ID. Relevant dates for training classes (including the Training Release Date) are housed in the "Training Classes" table, which is linked to the "Call Center Roster" through generated Class Keys.
Thank you for reading, and thanks in advance for any pointers you may have!
Solved! Go to Solution.
Remember that in Power BI, actions are very context-dependent.
For the total row, the default process that performs DAX reruns the same formula as for each row in the table. But that's not what you want.
Here's an example of a possible solution:
Answered Since Release =
if( isinscope(tabla[Class Key])
Result for Table Rows
,CALCULATE( SUM('User Call Details'[ACD Calls Answered]),
FILTER(DatesTable,
DatesTable[Date]>=max('Training Classes'[Training Release Date])))
resultao para la row de total
,
SUMX(
summarize(
board
,tabla[Class Key]
, 'Training Classes'[Training Release Date]
,"Total", CALCULATE( SUM('User Call Details'[ACD Calls Answered]),
FILTER(DatesTable,
DatesTable[Date]>=max('Training Classes'[Training Release Date])))
)
, [Total]
)
)
If you liked the answer, a kudo helps
Remember that in Power BI, actions are very context-dependent.
For the total row, the default process that performs DAX reruns the same formula as for each row in the table. But that's not what you want.
Here's an example of a possible solution:
Answered Since Release =
if( isinscope(tabla[Class Key])
Result for Table Rows
,CALCULATE( SUM('User Call Details'[ACD Calls Answered]),
FILTER(DatesTable,
DatesTable[Date]>=max('Training Classes'[Training Release Date])))
resultao para la row de total
,
SUMX(
summarize(
board
,tabla[Class Key]
, 'Training Classes'[Training Release Date]
,"Total", CALCULATE( SUM('User Call Details'[ACD Calls Answered]),
FILTER(DatesTable,
DatesTable[Date]>=max('Training Classes'[Training Release Date])))
)
, [Total]
)
)
If you liked the answer, a kudo helps
Wow, thanks for the quick reply!
This seems to have solved the problem, so kudos for sure! I knew about the context-dependency, but I hadn't encountered it in a case like this. A summary table hadn't occured to me at all. There are some trickier calculations in this same vein I still need to tackle, but this is an awesome guide.
I'd also never seen ISINSCOPE, which I could've used many, many times prior to this. Gracias!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
106 | |
93 | |
69 |
User | Count |
---|---|
167 | |
132 | |
130 | |
96 | |
91 |