Find and return intersection points between lines

[Simon_G]

Hello,

I'm working on an analysis comparing scenarios and I would like to find the intersection point(s) between lines. Below is an example of a graph I can plot and I would like to calculate at what temparature the 2 lines meet. 

The data is stored in different tables for different scenarios but are linked by the temperature. The temperature is at 0.1C increment. Below is the example of the datatables I have. I have diffirent scenarios/variables and use scliers to display them on a graph. The graph example shows sum(GHG value 1) and sum(GHG value 2). I would also create measures the would sum GHG values 1 & 2 as one and plot another line for sum GHG values 2 & 3 for example.  

Temperature	Scenarios 1	Additional variables	GHG value 1
0	Scenario a	Variable 2	395
0.1	Scenario a	Variable 2	391
0.2	Scenario a	Variable 2	387
0	Scenario b	Variable 2	450
0.1	Scenario b	Variable 2	432

Temperature	Scenarios 2	Additional variables	GHG value 2
0	Variable 1	Variable 2	395
0.1	Variable 1	Variable 2	391
0.2	Variable 1	Variable 2	387

Do you know of a DAX measure that could be used to find the interect point(s) (could be several) and what temperature it is?

Thank you for your help. 

[v-csrikanth]

Hi Simon_G,

Thank you for reaching out to the Microsoft Community Forum.

This DAX measure will create a table (IntersectionPoints) that captures the temperature at which the lines intersect. It calculates the difference between the GHG values from the two tables and filters the results to find the points where the absolute difference is less than 0.1, indicating an intersection.

You can use this measure to analyze the intersection points and the corresponding temperatures for your scenarios.

DAX code :

IntersectionPoints = 
VAR TempTable1 = 
    SELECTCOLUMNS (
        Table1,
        "Temperature", Table1[Temperature],
        "GHGValue1", Table1[GHG value 1]
    )
VAR TempTable2 = 
    SELECTCOLUMNS (
        Table2,
        "Temperature", Table2[Temperature],
        "GHGValue2", Table2[GHG value 2]
    )
VAR CombinedTable = 
    NATURALINNERJOIN ( TempTable1, TempTable2 )
VAR IntersectionPointsTable = 
    FILTER (
        ADDCOLUMNS (
            CombinedTable,
            "Difference", [GHGValue1] - [GHGValue2]
        ),
        ABS ( [Difference] ) < 0.1
)
RETURN
    SELECTCOLUMNS (
        IntersectionPointsTable,
        "Temperature", [Temperature],
        "GHGValue1", [GHGValue1],
        "GHGValue2", [GHGValue2]
    )

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[Simon_G]

Thank you for the response. I wrote the dax as noted above but I come to this error:
"An incompatible join column, (''[Temperatures]) was detected. 'NATURALINNERJOIN' doesn't support joins by using columns with different data types or lineage." All data is decimal number so I'm not sure what the issue is. 
Thank you

[Deku]

DAX has a concept know as lineage, it knows for given values, what column they originated from. Added a TREATAS, to change the lineage of the temperature from temp table2

IntersectionPoints =
VAR TempTable1 =
SELECTCOLUMNS (
Table1,
"Temperature", Table1[Temperature],
"GHGValue1", Table1[GHG value 1]
)
VAR TempTable2 =

TREATAS(
SELECTCOLUMNS (
Table2,
"Temperature", Table2[Temperature],
"GHGValue2", Table2[GHG value 2]
),

Table1[Temperature],

Table2[GHG value 2]

)

VAR CombinedTable =
NATURALINNERJOIN ( TempTable1, TempTable2 )
VAR IntersectionPointsTable =
FILTER (
ADDCOLUMNS (
CombinedTable,
"Difference", [GHGValue1] - [GHGValue2]
),
ABS ( [Difference] ) < 0.1
)
RETURN
SELECTCOLUMNS (
IntersectionPointsTable,
"Temperature", [Temperature],
"GHGValue1", [GHGValue1],
"GHGValue2", [GHGValue2]
)

[Simon_G]

Thank you for the response, it seems to have that issue, now I get "No common join columns detected. The join function 'NATURALINNERJOIN' requires at-least one common join column.".

All temperatures column are linked to a temperature table (that just list all the temperature once)

[Deku]

Adjusted the start to consider the temperature table, assume relationship to the other two tables

IntersectionPoints =

VAR tbl =

Addcolumns(

Temperatures[temperature],

"GHGValue1", 

Calculate( max( Table1[GHG value1])),

"GHGValue2", 

Calculate( max( Table2[GHG value2]))

)

VAR IntersectionPointsTable =

FILTER (

ADDCOLUMNS (

Tbl,

"Difference", [GHGValue1] - [GHGValue2]

),

ABS ( [Difference] ) < 0.1

)

RETURN

SELECTCOLUMNS (

IntersectionPointsTable,

"Temperature", [Temperature],

"GHGValue1", [GHGValue1],

"GHGValue2", [GHGValue2]

)

[Simon_G]

Thank you for the reply. Unfortunatly I still have an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

[Deku · Accepted solution]

replace

SELECTCOLUMNS (
   IntersectionPointsTable, 
   "Temperature", [Temperature],
   "GHGValue1", [GHGValue1],
   "GHGValue2", [GHGValue2]
)

with

CONCATENATEX(
   IntersectionPointsTable,
   [Temperature],
   ", ",
   [Temperature], ASC
)

[Simon_G]

Looks like it worked in the end. I just had to change Max to Sum. 

Thank you!

[v-csrikanth]

Hi @Simon_G 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[Simon_G]

Hello, I still haven't found a solution to my problem (but it seems there isn't an easy solution to it).

[v-csrikanth]

Hi @Simon_G 

Thank you for reaching out to the Microsoft Community Forum.
Could you please provide more details, including the steps you have taken so far and what you are expecting? These details will help us better understand your issue and guide you in solving it.
Could you please share the expected output snapshot.

Thank you.

[Deku]

You can linestx to get the y = mx + a for the straight line.

The curved one will be more difficult.

If you had both definitions you could solve exactly with some algebra. 

You could estimate the points. Would that be acceptable?

[Simon_G]

That would be complicated as some of the data is more in buckets (is flat for a few temperature points then changes) and some is not linear (like example above)