Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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
Proud to be a Super User!
Paul on Linkedin.
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:
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |