Lookup from another Table with multiple conditions including a range

[anhe91]

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é

[anhe91 · Accepted solution]

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]

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