Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
anhe91
Frequent Visitor

Lookup from another Table with multiple conditions including a range

Hi everyone,

 

I am a beginner in Power BI and coming from Excel, as the most of us.

 

My challenge is that I have two tables. In table 1 there are entries with speed limits. The position of the speed limits is based on a hierarchy in the columns road number, road section (every road has sections from 1 to n) and mileage.

In table two is the data about the road network. Every entry is a road subsection (from junction to junction) which is based on a hierarchy in the columns, road number, road section and mileage from, mileage to.

My goal is to add a column in table 1 which shows in which subsection from table two the speed limit fits. The formula has to proof, if the road number and the road section are the same and if the mileage from the speed limit is between mileage from and mileage to.

An additional information is, that mileage from can be greater or smaller as mileage to.

 

I hope someone can help me.

 

Thank you!

Best regards, André

1 ACCEPTED SOLUTION
anhe91
Frequent Visitor

I could solve it this way:
 
RoadSubsection =
MAXX(
    FILTER(
        Table2,
         Table2[RoadNumber] = Table1[RoadNumber]
         && Table2[RoadSection] = Table1[RoadSection]
         && (
                (Table2[MileageFrom] <= Table1[Mileage] && Table2[MileageTo] >= Table1[Mileage])
                || (Table2[MileageTo] <= Table1[Mileage] && Table2[MileageFrom] >= Table1[Mileage])
         )
    ),
    Table2[RoadSubsection]
)

View solution in original post

2 REPLIES 2
anhe91
Frequent Visitor

I could solve it this way:
 
RoadSubsection =
MAXX(
    FILTER(
        Table2,
         Table2[RoadNumber] = Table1[RoadNumber]
         && Table2[RoadSection] = Table1[RoadSection]
         && (
                (Table2[MileageFrom] <= Table1[Mileage] && Table2[MileageTo] >= Table1[Mileage])
                || (Table2[MileageTo] <= Table1[Mileage] && Table2[MileageFrom] >= Table1[Mileage])
         )
    ),
    Table2[RoadSubsection]
)
Anonymous
Not applicable

Hi @anhe91 

 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

 

 

 

Best Regards,

Jayleny

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.