Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Solved! Go to 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
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
It 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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I aggree.But is there any other way? I need that column to compute next steps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |