How to optimize Running total query

[Sandeep_4b5]

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] ) )

[Anonymous · Accepted solution]

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

[Sandeep_4b5]

It is one of the solution.But I want to try it by using DAX or PQ.

[CNENFRNL]

No optimization of PQ query is match for DAX measure to cope with such running total scenarios; DAX is born for it.

[Sandeep_4b5]

I aggree.But is there any other way? I need that column to compute next steps.