Max Age for Bottom 80%, Based On Current Filter Context

[ChuckChuck]

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%

[v-easonf-msft]

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. 

[parry2k]

@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.⚡

[ChuckChuck]

Hello @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.