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

[sanrajbhar]

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 power bi Enviroment ? Below is snpashot for more information.

[PaulDBrown]

@sanrajbhar 

You will need to set up your tables by columns first (pivot them in Power Query). 
Then you can use a measure to "filter" IDs by the criteria you need.

can you share actual sample data (with more IDs) instead of an image?

please read this thread:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

[sanrajbhar]

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.

[v-kelly-msft]

Hi  @sanrajbhar ,

First add an index column in both 2 tables;

Then create a new table as below:

Union Table = UNION('Table A','Table B')

Then create 2 measures,and you will see:

For details, pls see attached.

 

Best Regards,
Kelly

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

[sanrajbhar]

Probably I am not able to put Question correctly .I have updated Question (screenshot) what I wished to do. thanks

[v-kelly-msft · Accepted solution]

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!