Index cycles in DAX

[mangaus1111]

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

[PhilipTreacy · Accepted solution]

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.

Download sample PBIX file

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
    Result

Regards

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]

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.