How to detect changes to values through a specific order

Given

Id | RouteId | Milepoint | RoadCondition 
-: | :------ | --------: | :------------ 
 1 | A       |         0 | X             
 2 | A       |         1 | X             
 3 | A       |         4 | Y             
 4 | B       |         0 | Y             
 5 | B       |         2 | null 
 6 | B       |         5 | null 
 7 | B       |         6 | Z             
 8 | B       |        18 | Z             
 9 | C       |         0 | X             

I am after

Id | RouteId | Milepoint | RoadCondition | ContinuousId
-: | :------ | --------: | :------------ | -----------:
 1 | A       |         0 | X             |            1
 2 | A       |         1 | X             |            1
 3 | A       |         4 | Y             |            2
 4 | B       |         0 | Y             |            3
 5 | B       |         2 | null          |            4
 6 | B       |         5 | null          |            5
 7 | B       |         6 | Z             |            6
 8 | B       |        18 | Z             |            6
 9 | C       |         0 | X             |            7

DECLARE @Example TABLE (
  Id INT primary key,
  RouteId varchar(50),
  Milepoint INT,
  RoadCondition varchar(50),
  ContinuousId INT
)

db<>fiddle here

I am using SQL Server 2017.

What I am after is the ability to generate the ContinuousId, which travels along RouteId ASC, Milepoint ASC and when the RouteId or RoadCondition changes, it increments the ContinuousId

I know that I’ll want to use LEAD() but handling NULLs is the part I am not sure how to approach the problem, because I can’t just COALESCE the NULLs because no two nulls can be assumed to be the same value, it should increment whenever it sees a NULL for RoadCondition

Answer

Not as clever as DENSE_RANK but I think this works as well

DECLARE @Example TABLE (
    Id INT primary key,
    RouteId varchar(50),
    Milepoint INT,
    RoadCondition varchar(50)
)


INSERT INTO @Example
VALUES 
    (1, 'A', 0, 'X')
    ,(2, 'A', 1, 'X')
    ,(3, 'A', 4, 'Y')
    ,(4, 'B', 0, 'Y')
    ,(5, 'B', 2, NULL)
    ,(6, 'B', 5, NULL)
    ,(7, 'B', 6, 'Z')
    ,(8, 'B', 18, 'Z')
    ,(9, 'C', 0, 'X')

;WITH CTE AS(
    SELECT *
    ,CASE 
        WHEN                    
            RouteId = LAG(RouteId) OVER (PARTITION BY RouteId ORDER BY Milepoint) 
            AND
            RoadCondition = LAG(RoadCondition) OVER (PARTITION BY RouteId ORDER BY Milepoint) 
        THEN 0
        ELSE 1
    END AS StartOfNextIsland 
    FROM @Example
)

SELECT *, SUM(StartOfNextIsland) OVER (ORDER BY RouteId, Milepoint) AS ContinuousId
FROM CTE