Forum Discussion
Find and return intersection points between lines
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.
replace
SELECTCOLUMNS ( IntersectionPointsTable, "Temperature", [Temperature], "GHGValue1", [GHGValue1], "GHGValue2", [GHGValue2] )with
CONCATENATEX( IntersectionPointsTable, [Temperature], ", ", [Temperature], ASC )
13 Replies
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?
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)
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.
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!Hello, I still haven't found a solution to my problem (but it seems there isn't an easy solution to it).
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
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 youDAX 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]
)
