Forum Discussion
How to optimize Running total query
I am trying to find the Running total by grouby different conditions.The following is working but its taking much time to load the data.Help me to optimize this formula.
BufferedTable = Table.Buffer(#"Added Index")
#"Running Total"= Table.AddColumn( BufferedTable, "Running Total", (OutTable) => List.Sum( Table.SelectRows( BufferedTable, (InTable) => InTable[Index] <= OutTable[Index] and InTable[Category] = OutTable[Category]
and
InTable[Brand] = OutTable[Brand]
)[Amount] ) )
HI Sandeep_4b5,
What type of data source are you worked on?
If you are working with a type of datasource that supports advanced queries(e.g. t SQL), you can try to add a custom query to directly use the query to get the result table with expected calculation fields. This result query table should have better performance than do complex summarize/aggeration by m query functions.
Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs
Regards,
Xiaoxin Sheng
4 Replies
HI Sandeep_4b5,
What type of data source are you worked on?
If you are working with a type of datasource that supports advanced queries(e.g. t SQL), you can try to add a custom query to directly use the query to get the result table with expected calculation fields. This result query table should have better performance than do complex summarize/aggeration by m query functions.
Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs
Regards,
Xiaoxin ShengIt is one of the solution.But I want to try it by using DAX or PQ.
No optimization of PQ query is match for DAX measure to cope with such running total scenarios; DAX is born for it.
I aggree.But is there any other way? I need that column to compute next steps.
