Forum Discussion

ChuckChuck's avatar
ChuckChuck
Icon for Helper I rankHelper I
5 years ago

Max 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]. S...
Need Help
v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
ChuckChuck
Icon for Helper I rankHelper I
5 years ago

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.