Forum Discussion

sanrajbhar's avatar
sanrajbhar
Frequent Visitor
5 years ago
Solved

Join table based on value from second table if value is withing range of those column values

Any guide how one can join table based on value from table1 less than or greate than / between two value from table2. it looks fairly doable in sql. but i have csv table how can I achive similar in p...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi sanrajbhar ,

     

    First create an index column in table B;

    Then create a calculated column as below:

     

    Column = 
var _start=CALCULATE(MAX('Table B'[Column 2]),FILTER('Table B','Table B'[Column 1]="Start chainage"))
var _index=CALCULATE(MAX('Table B'[Index]),FILTER('Table B','Table B'[Column 2]=_start))
var _end=CALCULATE(MAX('Table B'[Column 2]),FILTER('Table B','Table B'[Index]=_index+1))
var _chain=CALCULATE(MAX('Table A'[Column2]),FILTER('Table A','Table A'[Column1]="Chainage"))
Return
IF('Table A'[Column1]="Chainage",
IF(_chain>=_start&&_chain<=_end,CALCULATE(MAX('Table B'[Column 2]),FILTER('Table B','Table B'[Index]=_index-1)),BLANK()))

     

     

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

  • sanrajbhar's avatar
    sanrajbhar
    Frequent Visitor
    5 years ago

    thanks for reply. here IDs are not important. I need to match if chainage is between chainage_start and chainage_end and do the joins.