Forum Discussion
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 power bi Enviroment ? Below is snpashot for more information.
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!
5 Replies
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
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.
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,
KellyDid I answer your question? Mark my post as a solution!
