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
PhilippeM
New Member

Search in a table for the value range in which a reference value located in another table

Hello, (from France)

 

The first table contains X rows with a column of values

 

 

Value
100
1000
5000
12000

 

The second table has Four columns

 

Minimum valueMax valueMinimum scaleMax scale
030002060
300160004080
600110000200300
10000 350 

*10000 and more

 

I would like to add a column to my first table which gives me the minimum applicable scale

 

 

Valuescale
10020
100020
500040
12000350

 

A big thank you for your help

 

Philippe

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @PhilippeM,

 

You can use the FILTER function to perform this lookup:

 

scale = 
VAR CurrentValue = 'Table1'[Value]
RETURN
    MINX(
        FILTER(
            'Table2',
            CurrentValue >= 'Table2'[Minimum value] &&
            (CurrentValue <= 'Table2'[Max value] || ISBLANK('Table2'[Max value]))
        ),
        'Table2'[Minimum scale]
    )

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

1 REPLY 1
Sahir_Maharaj
Super User
Super User

Hello @PhilippeM,

 

You can use the FILTER function to perform this lookup:

 

scale = 
VAR CurrentValue = 'Table1'[Value]
RETURN
    MINX(
        FILTER(
            'Table2',
            CurrentValue >= 'Table2'[Minimum value] &&
            (CurrentValue <= 'Table2'[Max value] || ISBLANK('Table2'[Max value]))
        ),
        'Table2'[Minimum scale]
    )

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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.