Forum Discussion
looping using DAX
Hello Everyone,
I am very new with power BI and please I need help from expert.
I need the DAX translation of this For Loop:
a = 0;
b = 0;
for (int i=1; i<=n; i++)
{
a = x - b;
b = x;
}
In this loop, x and n are a predefined values that I will get from a table.
please help.
Anonymous this is a simple time intelligence calculation, and as a best practice, it is recommended to add a date dimension to your model to work with dates, and here is one of my post that talk about how to add a date dimension.
Once it is done, just add the following measures, this can be done in one measure too, but I like to break these down for better understanding and also to reuse these measure (if required)
Sum Current = SUM ( Table[Current] ) Sum Prev Day = CALCULATE ( [Sum Current], DATEADD ( 'Calendar'[Date], -1, DAY ) ) Increase = [Sum Current] - [Sum Prev Day]Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
16 Replies
Anonymous this is a simple time intelligence calculation, and as a best practice, it is recommended to add a date dimension to your model to work with dates, and here is one of my post that talk about how to add a date dimension.
Once it is done, just add the following measures, this can be done in one measure too, but I like to break these down for better understanding and also to reuse these measure (if required)
Sum Current = SUM ( Table[Current] ) Sum Prev Day = CALCULATE ( [Sum Current], DATEADD ( 'Calendar'[Date], -1, DAY ) ) Increase = [Sum Current] - [Sum Prev Day]Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Anonymous finally, so glad to hear it. Cheers!!
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
You really can't do recursive calculations in DAX. You can do it with List.Accumulate in the query editor though.
List.Accumulate Hidden Gem of Power Query List Functions in Power BI - RADACAD
Regards,
Pat
Anonymous can you share the code you are using in the advanced editor?
I found something that could be easier if I understand how it works. this method is using calculated column and here is the code:
For Loop = // Provide some starting values VAR __n = 5 VAR __sum = 0 // Generate a "loop table", this will emulate a for loop for i=1 to some number VAR __loopTable = GENERATESERIES(1,__n) // Add in our calculated sum, emulating calculations done as iterations over the loop VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + SUMX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value])) // Determine our MAX interation, the maximum value for "i" VAR __max = MAXX(__loopTable1,[Value]) RETURN // Return the value associated with the maximum value of "i" which is the last iteration in our "loop" MAXX(FILTER(__loopTable1,[Value]=__max),[__sum])
My problem now is that I need to adapt this part:
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + SUMX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value]))
so that it will do the calculation I want.
thanksthis is basically what I need to do: in this table, I have the values for CURRENT and I need a code to calculate INCREASE, which is the difference between CURRENT for the current day and the day before. Not to forget that the size of the table is not fixed. that is why I was using a loop from 1 to the actual size.
CURRENT INCREASE
63 63
135 72
212 77
290 78
350 60Thank You
Anonymous I'm wondering if this is really the correct approach. By looking at your requirement, seems like maybe looping is not required. You started the post with the looping requirement, but I think if you share your sample data and expected output, it is probably a much simpler solution.
Anonymous Is that the only column, Current. How do you know the order? Is the order base from small to big or there is a date or index column?
Anonymous did you marked your date table as the date and also when you are visualizing the data, are you using date column from the date table.
