Forum Discussion

Simon_G's avatar
Simon_G
Frequent Visitor
1 year ago
Solved

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 wha...
"DAX "
  • Deku's avatar
    Deku
    1 year ago

    replace

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

    with

    CONCATENATEX(
   IntersectionPointsTable,
   [Temperature],
   ", ",
   [Temperature], ASC
)
v-csrikanth's avatar
v-csrikanth
Community Support
1 year ago

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's avatar
Simon_G
Frequent Visitor
1 year ago

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's avatar
    Deku
    Super User
    1 year ago

    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's avatar
      Simon_G
      Frequent Visitor
      1 year ago

      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's avatar
        Deku
        Super User
        1 year ago

        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]

        )