custom index for column

[Anonymous]

Hello

I am in need of a little help. I have a data sheet wich describes 1600 different objects (1 object per row). Each object has a different logical ID (such as: A-01-01-01, A-01-01-02 etc...). Now a part of these objects are static and do not move over time. The other part is dynamic, wich means the objects change over time.

The logical ID for each object stays the same and is only used to recognize the object. But since the dynamic objects change, their ID needs to change too. I have created a index column for these since the logical ID stays the same. I also have a column wich describes if the object is dynamic or not, if it is not, i return the value "False"

Now my problem is this: The dynamic objects need to change their index according to the column wich describes if it is dynamic or not. Each dynamic object exists of 9 objects (each with their unique logical ID). I need to create a custom column (or something else if that would work better) wich alters the index so that the index of the static objects runs normally, but once it reaches a dynamic object, it needs to look at the input data and change according to that. 

for example:

- static objects: 1-5

- dynamic objects: 1-5

index: 1-2-3-4-5-6-7-8-9-10-false-false-false-false

As you can see hopefully, the static objects have a standard index, but the dynamic objects continue with 6 up till 10 (the 5th dynamic object) and then return false for the other 4 dynamic objects.

Is there a way how i could best do this or could anyone help me out? 

[rubayatyasmin · Accepted solution]

Hi, @Anonymous 

You can use DAX RANKX() func to achieve you goal. 

refer: https://learn.microsoft.com/en-us/dax/rankx-function-dax

sample DAX idea:

Custom Index = 
VAR CurrentLogicalID = [Logical ID]
VAR CurrentDynamic = [Dynamic]
VAR StaticCount = COUNTROWS(FILTER(YourTable, YourTable[Dynamic] = "False"))
RETURN
IF(CurrentDynamic = "False", "False", StaticCount + RANKX(FILTER(YourTable, YourTable[Dynamic] = "True"), [Logical ID], , ASC) + 5)

also I found some thread that would help you give idea,

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Expression-to-create-dynamic-Index-column-to-show-all/m-p/2651611

https://community.fabric.microsoft.com/t5/Power-Query/Create-a-dynamic-index-column-based-on-Date-Selection-by-user/td-p/2711513

https://community.fabric.microsoft.com/t5/Power-Query/To-create-a-DYNAMIC-index-column-using-DAX-based-on-Rank-column/td-p/2032294

[DallasBaba]

@Anonymous 

First, create a new column in your table that contains the index values for the dynamic objects.
You can do this by using the Table.SelectRows function to select the rows where the object is dynamic and then using the List.Numbers function to generate a list of index values starting from 6 up to 10 (or any other value you want).
You can then use the Table.AddColumn function to add this new column to your table.

Next, create another new column that will contain the final index values for each row.
You can do this by using the Table.AddColumn function again to add a custom column that applies a custom function to each row of your table.
The function should check if the object is dynamic or not and then return either the value of the new column (if it is dynamic) or the original index value (if it is static).

For example :

let
Source = <your data source>,
#"Added Custom" = Table.AddColumn(Source, "DynamicIndex", each if [Dynamic] then List.Numbers(6, 5) else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "DynamicIndex"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "FinalIndex", each if [Dynamic] then [DynamicIndex] else [Index] + 1)
in
#"Added Custom1"

This will output a new table with two additional columns: DynamicIndex and FinalIndex.
The DynamicIndex column contains the index values for the dynamic objects, and the FinalIndex column contains the final index values for each row based on whether it is dynamic or not.