Forum Discussion
Solution SageIndex cycles in DAX
Hi community,
do you know how I can get the below result in DAX? If it is not possible, then it is ok Power Query too.
Row Data Expected Result
1 1
1 1
0 2
0 2
1 3
0 4
0 4
1 5
1 5
0 6
0 6
0 6
1 7
0 8
1 9
1 9
Hi mangaus1111
** EDIT - I see HotChilli beat me to it. Sorry it took me a while to respond as I had to modify and test my code in between getting kids off to school. HotChilli answered before me but I'll leave this here as an alternative.
You can use List.Generate. I modified code I wrote to calculate Grouped Running Totals
(values as list) as list => let Result = List.Generate ( ()=> [ Flips = 1, i = 0 ], each [i] < List.Count(values), each try if values{[i]} = values{[i] + 1} then [Flips = [Flips] , i = [i] + 1] else [Flips = [Flips] + 1, i = [i] + 1] otherwise [i = [i] + 1] , each [Flips] ) in ResultRegards
Phil
6 Replies
- PhilipTreacy
Super User
Hi mangaus1111
** EDIT - I see HotChilli beat me to it. Sorry it took me a while to respond as I had to modify and test my code in between getting kids off to school. HotChilli answered before me but I'll leave this here as an alternative.
You can use List.Generate. I modified code I wrote to calculate Grouped Running Totals
(values as list) as list => let Result = List.Generate ( ()=> [ Flips = 1, i = 0 ], each [i] < List.Count(values), each try if values{[i]} = values{[i] + 1} then [Flips = [Flips] , i = [i] + 1] else [Flips = [Flips] + 1, i = [i] + 1] otherwise [i = [i] + 1] , each [Flips] ) in ResultRegards
Phil
- mangaus1111
Hi PhilipTreacy ,
your solution is simply amazing. Thank you. I feel like now studing advanced M.
Do you know if online there are some free resources for this kind of queries in M?
- PhilipTreacy
No worries mangaus1111
Sorry I can't recommend any single source to learn M. There are lots of good places on the net, you'll just have to work through them and see what clicks with you.
I'm the same as most I guess, lots of Googling, writing code for hours figuring stuff out and quite a lot of banging my head against the wall with things like List.Generate. I wish there was a debugger for M.
Cheers
Phil
- vojtechsima
Hi, PhilipTreacy ,
could you please briefly explain this part of your code, I tried to come up with something similar using List.Generate but couldn'T quickly figure out:if values{[i]} = values{[i] + 1} then [Flips = [Flips] , i = [i] + 1] else [Flips = [Flips] + 1, i = [i] + 1]
Thank you- PhilipTreacy
Hi vojtechsima
Sure,
Flips holds the value that indicates how often the values have changed from 1 to 0 and vice versa. Initially Flips is 1.
i is a counter that is used to index the values in the Data column.
if values{[i]} = values{[i] + 1}If the current Data value is equal to the next Data value
then [Flips = [Flips] , i = [i] + 1]then the next value for Flips is the same as the previous value, increment the counter
else [Flips = [Flips] + 1, i = [i] + 1]else the Value in the Data column has changed either from 1 to 0 or vice versa, so the next value for Flips is the previous value + 1, increment the counter.
Every time the loop is run, the next value (from this current loop) is added to the list being generated.
Regards
Phil
- HotChilli
Community Champion
In Power Query, you could use Table.Group with GroupKind.Local to get the count of values like :
1,2
0,2
1,1
..
then
Add Index column,
and then using List.Repeat, add a column which is a list of each row's values. {1,1} {0,0} {1} ..
Expand the List to Rows and that should give you what you want
---
I reckon it would be possible to combine some of those steps in the Table.Group too but I would always start by doing them separately.
I've seen your posts, you know what you're doing, so have a go and I'll respond if more help required.
Good luck.
