Forum Discussion

zenton's avatar
zenton
Helper II
3 years ago
Solved

Loop though Iteration based on another column

Hi,

 

Problem 1
I need help with calculating the Bay in Use column.

Bay in u\Use starts with Bay1
When Column FULL = "Full" Bay in Use = Bay 2, then Bay 3, Bay 4

When Bay 4 is full the cycle starts again back to Bay 1
If possible I would like to make the Bays variable 1 to 4

 

Problem 2
If the bay is Full go to next bay. If no bays are available Bay in Use = "No Capacity"

The Bay is empty 13 working days after Full status as per Column Date Empty

 

Note: Running Totals and Loads are calculated in M Query

Thanks Rodney

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi zenton 

    You can refer to the following column

    Bay in use = var _filter=COUNTROWS(FILTER('Table',[Index]<=EARLIER('Table'[Index])&&[FUll]="Full"))
return "Bay"&" "&MOD(_filter,4)+1

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi zenton 

    You can create two calculated column

     

     

    Bay in use = var _filter=COUNTROWS(FILTER('Table',[Index]<=EARLIER('Table'[Index])&&[FUll]="Full"))
return IF(_filter>3,"Bay"&_filter+1-4,"Bay"&_filter+1)

Date Empty = IF([FUll]="Full",[Date]+13)

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • zenton's avatar
      zenton
      Helper II
      3 years ago

      Hi Anonymous , Thanks fio your help. The calcultion works for the 1st itteration only. Idex row 43 should be Bay1

      Thanks Rodney

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi zenton 

        You can refer to the following column

        Bay in use = var _filter=COUNTROWS(FILTER('Table',[Index]<=EARLIER('Table'[Index])&&[FUll]="Full"))
return "Bay"&" "&MOD(_filter,4)+1

        Best Regards!

        Yolo Zhu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.