Forum Discussion
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...
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,
KellyDid I answer your question? Mark my post as a solution!
Probably I am not able to put Question correctly .I have updated Question (screenshot) what I wished to do. thanks
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!