PowerBI: same IDs for consecutive data

Question

I have a question about generating IDs in power BI.

Question: I have the table like following:

I'd like to generate same IDs for consecutive data (it means that end date of previous row equals to start date of current row), that has equal values in column 'Type', but IDs have to be different, if data is not consecutive. The example of correct IDs are below.

(Note that 'Bike' has different Ids because these rows are NOT consecutive)

Can you help me please with writing DAX command or Query? Every advice would be very helpful.

Answer 1

I created 3 separate calculated columns in order to accomplish this , with the final IDs column being the result you're looking for. I've included the DAX below, just replace the "Table1" references with your table's name. Skip down to the bottom to see an image of how the final table looks (Results Table).

First Calculated Column

Create a unique string ID for each row in the table. Specify "Same" for rows that will inherit the previous unique ID. This makes use of the EARLIER function and a solution similar to Get Value from Previous Row post on the Community page in order to evaluate the previous row in a calculation.

First Iteration IDs = 
VAR previousRow = 
    TOPN(
        1, 
        FILTER(
            Table1, 
            Table1[Start Date] < EARLIER(Table1[Start Date])
            && Table1[Type] = EARLIER(Table1[Type])
        ), 
        [Start Date], 
        DESC
    )

VAR previousEndDate = MINX(previousRow, [End Date])
VAR previousType = MINX(previousRow, [Type])

RETURN 
    IF(
        Table1[Start Date] = previousEndDate && Table1[Type] = previousType, 
        "Same", 
        CONCATENATE(FORMAT(Table1[Start Date], "dd/mm/yyyy"), CONCATENATE(" ", Table1[Type]))
    )

Second Calculated Column

Convert all of the "Same" IDs from the previous column to their respective IDs in that column.

Second Iteration IDs = 
VAR previousRow = 
    TOPN(
        1, 
        FILTER(
            Table1, 
            Table1[Start Date] < EARLIER(Table1[Start Date])
            && Table1[Type] = EARLIER(Table1[Type])
            && NOT(Table1[First Iteration IDs] = "Same")
        ), 
        [First Iteration IDs], 
        DESC
    )

VAR previousID = MINX(previousRow, [First Iteration IDs])

RETURN 
    IF(
        Table1[First Iteration IDs] = "Same", 
        previousID, 
        Table1[First Iteration IDs]
    )

Third Calculated Column

Use the RANKX function to create more user-friendly number IDs from the string IDs in the previous column. This is the original column that you had asked for.

IDs = RANKX(Table1, Table1[Second Iteration IDs], , 1, Dense) - 1

I've included a results table below based on the sample table that you provided. You may need to play around with the unique string ID format or the RANKX function depending on how your actual data looks.