The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I've been struggling with this problem for weeks now, really need some help to sort it out.
I have a dynamically calculated measure [Aged Tickets Days] based on month selection. Then I added another measure [aged_total] to get the total number of tickets that are older than 30 days.
I was able to give 1 value to the rows that are over 30 in Aged tickets days. but as you may know, Power Bi is not very supportive in terms of getting totals of measure.
aged_total =
var aged = IF([Aged Tickets Days] > 30, 1, BLANK())
return IF(HASONEFILTER('All Tickets'[ID]),
aged,
SUMX(FILTER('All Tickets', [Aged Tickets Days] > 30), aged)
)
So I searched to find the solution but haven't been able to resolve it.
https://www.youtube.com/watch?v=Ka7Ds4EAjNQ
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Looks like these tricks only work when calculating column not measure.
Please look at my sample file, advise if I'm doing something wrong.
Thank you so much for your help!
@Anonymous , Try like
aged_total =
var aged = IF([Aged Tickets Days] > 30, 1, BLANK())
return IF(HASONEFILTER('All Tickets'[ID]),
aged,
SUMX(values('All Tickets'[ID]), aged)
)
Hi @amitchandak ,
Thanks for the reply.
I've tried it but it returns all tickets created in the selected month, not the tickets over 30 days.
@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
SumScoreMeasure = SUMX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
You should be able to combine that with the technique shown here:
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Thanks for commenting @Greg_Deckler
I've tried your methods at a very early stage but it didn't work.
I assume SUMX doesn't work well with a dynamically calculated measure.
Here is the sample data that I'm struggling with in case you want to have a look.
https://drive.google.com/file/d/1iNbfYCKafi4jVbtNrGHhCmhP6aC44Fky/view?usp=sharing
Thank you for your help!
@Anonymous , I think a formula like this should work.
sumx(Values(Table[ID]) ,if( datediff(max(Created date]), selecteddate(Date[Date]),Day) >30 && isblank(Table[Closed Date]),1,0))
Do you need a bucket on top of it?
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@amitchandak There is a sample data link available in my original post.
Here is the link https://drive.google.com/file/d/1iNbfYCKafi4jVbtNrGHhCmhP6aC44Fky/view?usp=sharing
Thank you so much for your help.
Your "Aged Tickets Days" measure seems inconsistent.
Please confirm that is intended.
In general it helps to compute totals by themselves, not involving other measures.
like so:
aged total2 := sumx('All Tickets',if(DATEDIFF('All Tickets'[Created Date],'All Tickets'[Closed Date],DAY)>30,1,0))
Thank you @lbendlin for confirming it.
Yes, it is intended because I want the measure to return different values based on the ticket status.
e.g.
If a ticket is not closed -> return not_closed
If a ticket is closed && status='closed' -> return closed
If a ticket is closed && status='resolved' -> return resolved
+ Thanks for the suggestion. The problem is I need to check whether tickets are aged at the selected month including not closed ones.
For instance, if I select "May" it should count all tickets that are over 30 days, then
datediff([created date], [closed date], Day)
will do the job but I can't get the tickets that are still open because it doesn't have the closed date value yet.
Please let me know if you need more clarification.
Look again at your Aged Ticket Days measure. It returns closed, ignoring result.
Sorry @lbendlin my bad, it's been fixed now.
I was trying different things before and forgot to change it back.
Still, this doesn't change the way it behaves in other measures though.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |