Forum Discussion
Helper IMax Age for Bottom 80%, Based On Current Filter Context
I have a table with millions of records where I'm trying to dynamically arrive at the max [Age] by [As of Date], but filtered to only where the [Running Total] is less than 80% of the [Date Total]. So in this example, the result is 35. My visual will list 12 dates with the max age just before the 80% threshold is reached within the current filter context. If the user filters on [Acct #], the result will update based on the current context. I've been able to get a version of a measure to work, but it takes 1+ mins to run.
| Measure | Measure | Measure | ||||
| As of Date | Acct # | Age | Amt | Running Total | Date Total | Running Total % |
| 11/1/2020 | 123 | 0 | $5,486.00 | $5,486.00 | $25,941.00 | 21.15% |
| 11/1/2020 | 123 | 15 | $8,648.00 | $14,134.00 | $25,941.00 | 54.49% |
| 11/1/2020 | 123 | 21 | $748.00 | $14,882.00 | $25,941.00 | 57.37% |
| 11/1/2020 | 123 | 35 | $3,314.00 | $18,196.00 | $25,941.00 | 70.14% |
| 11/1/2020 | 123 | 40 | $7,745.00 | $25,941.00 | $25,941.00 | 100.00% |
4 Replies
- parry2k
Super User
ChuckChuck can you share what existing measure you are using? Also if possible share sample pbix file to look into it.
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
ChuckChuckHello parry2k ,
PBIX: https://1drv.ms/u/s!AqaBEhuP2H_olAOBkyTxlctZXCWa?e=ONJJxS
Measure:Max Age <80% Threshold =MAXX(FILTER(SUMMARIZE('Table', 'Table'[As of Date], 'Table'[Age], "RT %", [Running Total %]), [Running Total %]<.8), 'Table'[Age])When this measure is used in my dataset that is millions of rows, it takes a very long time to return the results.
- v-easonf-msft
Community Support
Hi, ChuckChuck
Maybe you can take a try to store the temporary table in a var variable ,
Max Age <80% Threshold = VAR tab = FILTER ( SUMMARIZE ( 'Table', 'Table'[As of Date], 'Table'[Age], "RT %", [Running Total %] ), [Running Total %] < .8 ) RETURN MAXX ( tab, 'Table'[Age] )or change the temporary table to a calculated table before you need to call the table in the measure( although this will take up some storage )
Best Regards,
Community Support Team _ Eason- ChuckChuck
Thank you, v-easonf-msft! Unfortunately, this solution doesn't produce an improved result. My existing measure takes ~2.43 minutes to run, while your suggestion takes ~2.42 minutes to run. The second suggestion to have a table with the stored values won't work because users will need to apply filters from the filter pane and see the max aged days update accordingly, so I need something dynamic against the full table in the current filter context.
