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
)
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]

    )

    • Simon_G's avatar
      Simon_G
      Frequent Visitor
      1 year ago

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

        replace

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

        with

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