Custom gruping / Indexing - based on multiple criterias/variables

[micjensen]

Hi community

I'm trying to make a custom grouping/indexing based on various criterias.

I have a table containing data for people and their helicopter flights/departures. A Scheduled flight can be cancelled and the reservation status will then be "Cancelled" and a new scheduled time will be created for that person --> a new row is then created in the table.

I'm really lost on how to do this, when there is so many different variables that has an impact on the result. All tricks, advices and any sort of help would be highly appreciated, thanks a lot in advance.

I want to group/index the table in the following way:

[Anonymous · Accepted solution]

Hi  @micjensen ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Flag1 =
var _table=
SUMMARIZE(
    'Table','Table'[Index],"min",MINX(FILTER(ALL('Table'),
'Table'[Index]=EARLIER('Table'[Index])&&'Table'[Reservation Status]<>"Cancelled"),[Index])+1)
var _column=
SELECTCOLUMNS(_table,"min",[min])
var _if1=
IF(
    'Table'[Index] in _column,1,0)
var _if2=
IF(
    'Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[DaWinci ID]=EARLIER('Table'[DaWinci ID])),[Index]),1,0)
var _if3=
IF(
    _if1=_if2,_if1,_if1+_if2)
return
_if3

Flag2 =
SUMX(FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])),[Flag1])

3. Result:

Best Regards,

Liu Yang

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

[micjensen]

Amazing job - thanks a lot!!!!!!!