How to calculate the difference between two points that do not overlap

[Cookie_Dookie]

Hey there community, I have no idea how to search for this problem, so if there is a post out there please let me know as I had no luck. 
I basically have data that looks like the below, I basically need the delta beween top and bottom, so [top] - [bot], the issue is that most of the points are not along the same x data point, so I cant simply do [top] - [bot].

My thought to get this to work is this to connect each point, and artifically create points (so interpolate), it could be both bot and top or just one of them. With this logic I should be able to do [top] - [bot]. 

I was thinking of a y = mx approach, for the [bot] line and then I can calculate the intercept between a [top] point and the line [bot]

I literally have millions of rows, and not sure if this should be a DAX measure (likely Power Query?) and more importantly if it can actually be done?

Data structure looks like:

x-axis	elevation	Point Type
1082066.225	142.5	top
1082067.689	142	top
1082068.641	141.95	top
1082071.206	142.15	top
1082071.645	141.95	top
1082071.868	142	top
1082073.186	142.35	top
1082077.261	104.35	bot
1082078.316	226.8	top
1082084.402	332.8	top
1082089.316	334	top
1082091.509	297.1	top
1082101.344	188.75	top
1082105.961	134.15	bot
1082111.167	210.1	top
1082111.271	134.8	bot
1082115.266	125.55	bot
1082118.766	88.5	bot
1082120.923	177.65	top
1082122.428	95.75	bot
1082130.447	82.05	bot
1082130.749	184.35	top
1082134.674	184	top
1082140.669	83.35	bot
1082144.411	188.65	top
1082150.269	76.05	bot
1082154.233	192.25	top
1082160.715	59.15	bot
1082163.982	182.2	top
1082171.224	47.55	bot
1082173.503	183.95	top
1082181.58	46.55	bot
1082183.262	179.8	top
1082191.591	54.45	bot
1082193.012	175.5	top
1082201.266	42.75	bot
1082202.758	174.35	top
1082211.819	54.05	bot

[v-mdharahman]

Hi @Cookie_Dookie,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing difficulties with calculating the difference between two points. As @DataNinja777 have already responded to your query, kindly go through his response and check if it solves your issue.

I would also take a moment to thank @DataNinja777, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Cookie_Dookie,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @Cookie_Dookie,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[Cookie_Dookie]

Yes I did!  I have millions of rows so I ran it via Power Query, and it definately worked. Slightly different variant for the dataset I have.

The code I used:

let
    // 1. Load your data
    Source = #"Elevation Data",

    // 2. Separate top (top) and bot (bot) points
    topPoints = Table.SelectRows(Source, each [Elevation Type] = "top"),
    botPoints = Table.SelectRows(Source, each [Elevation Type] = "bot"),

    // 3. Sort bot points by X_AXIS_M (ascending)
    botPointsSorted = Table.Sort(botPoints, {{"X_AXIS_M", Order.Ascending}}),

    // 4. Function to interpolate bot elevation at a given X_AXIS_M
    Interpolatebot = (x as number) as nullable number =>
        let
            // Find all bot points with X_AXIS_M less than or equal to x
            LowerRows = Table.SelectRows(botPointsSorted, each [X_AXIS_M] <= x),
            // Find all bot points with X_AXIS_M greater than x
            UpperRows = Table.SelectRows(botPointsSorted, each [X_AXIS_M] > x),
            // Get the last lower point
            Lower = if Table.RowCount(LowerRows) > 0 then LowerRows{Table.RowCount(LowerRows)-1} else null,
            // Get the first upper point
            Upper = if Table.RowCount(UpperRows) > 0 then UpperRows{0} else null,
            // If both points exist, interpolate
            Interpolated = if Lower <> null and Upper <> null and Lower[X_AXIS_M] <> Upper[X_AXIS_M]
                then
                    Lower[elevation] + (x - Lower[X_AXIS_M]) * (Upper[elevation] - Lower[elevation]) / (Upper[X_AXIS_M] - Lower[X_AXIS_M])
                else null
        in
            Interpolated,

    // 5. Add interpolated bot elevation to each top point
    topWithbot = Table.AddColumn(
        topPoints,
        "Interpolated bot",
        each Interpolatebot([X_AXIS_M]),
        type number
    ),

    // 6. Calculate Delta (top - Interpolated bot)
    Result = Table.AddColumn(
        topWithbot,
        "Delta",
        each [elevation] - [Interpolated bot],
        type number
    ),
    #"Filtered Rows" = Table.SelectRows(Result, each [Interpolated bot] <> null and [Interpolated bot] <> "")
in
    #"Filtered Rows"

[DataNinja777 · Accepted solution]

Hi @Cookie_Dookie ,

To calculate the difference between non-overlapping top and bot elevation points, you need to interpolate one or both datasets so that they align on the same x-axis. Since the x-values are not matched across the two elevation types, you can’t simply subtract [top] - [bot]. Instead, in Power Query, start by extracting a master list of unique x-values across both top and bot data using:

CombinedX = List.Distinct(YourTable[x-axis])

Then split your original table into two separate queries—one for top and one for bot—like this:

TopTable = Table.SelectRows(YourTable, each [Point Type] = "top")
BotTable = Table.SelectRows(YourTable, each [Point Type] = "bot")

Now you need to reindex and interpolate each table to fill in missing x-axis values. To do this, perform a left join from the CombinedX list to each of the top and bot tables. This will give you rows with null elevations where values are missing. Then sort the joined table by x, add an index, and for rows with missing elevations, identify the previous and next known values and interpolate using this formula:

Interpolated = y1 + (x - x1) * ((y2 - y1) / (x2 - x1))

Where x is the missing point, and (x1, y1) and (x2, y2) are the bounding known data points. You can extract these by grouping and adding custom columns with List.FirstN and List.Skip operations or using buffer + index tricks. Once both top and bot tables are interpolated with the same set of x-values, merge them together on x and compute the difference:

Delta = [Top Elevation] - [Bot Elevation]

This will give you the vertical gap between the two elevation lines at every point, even when the original data did not overlap. If you're dealing with millions of rows, do this logic inside Power Query rather than DAX to avoid performance bottlenecks.

Best regards,