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

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

Reply
frw
Frequent Visitor

Calculating Single Measure from Different Start Dates

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.

Measure Display.png

 

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.

55525c65-0661-44c4-adea-775ff0525f72.png

Thank you for reading, and thanks in advance for any pointers you may have!

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

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

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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