Direct Query 400Million Row of Data - Performance optimization (mal)

[Anonymous]

Hi I I have a direct query with SQL sitting on AWS cloud. The connection to the cloud is quick (so no issue there). Using performance analyzer I am expereincing 65secs visual refresh and I am aiming to get to 15secs. I have zero Calculated Columns and probably 15 calculated Measures. In this 400M row of data consist of data for Year 2019 and Year 2020 down to 10minutes interval. Can you offer any other option for me to improve perfromance from 65 secs to 15secs The DAX measures that takes the longest refresh is = Calculate(Sum(Table[datacolumn]), Filter(Table,[year]="2020") / Calculate(Sum(Table[datacolumn]), Filter(Table,[year]="2019")...thank u

[amitchandak · Accepted solution]

@Anonymous , refer if composite model can help in this case

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fast-and/td-p/712871

[Anonymous]

wow.. this is it!