Forum Discussion

mangaus1111's avatar
mangaus1111
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

Index 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 ...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    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

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
3 years ago

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.