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
justinblackmor
Regular Visitor

LookUp a value based on a column value between two columns in unrelated table

How can I return a value from Table B based on the account number in Table A being between a range set in Table B. 

 

Table A:

justinblackmor_0-1615239980235.png

 

Table B:

justinblackmor_2-1615240057065.png

 

 

I want to create a column in Table A returnining values from the "Class" column in Table B based on the account number from Table A falling in the range of Table B's "SEG 3 BEgin" and "SEG 3 End". For example, I want the value "Operating Expense" returned for a Table A account "4005". 

 

 

1 REPLY 1
dedelman_clng
Community Champion
Community Champion

Hi @justinblackmor -

 

Try creating a column on Table A with this kind of DAX pattern

 

Class =
VAR __ThisRow = TableA[Account]
VAR __minAcc =
    MAXX (
        FILTER (
            ALL ( TableB ),
            __ThisRow >= TableB[SEG3 Begin]
                && __ThisRow <= TableB[SEG 3 End]
        ),
        TableB[SEG 3 Begin]
    )
VAR __maxAcc =
    MINX (
        FILTER (
            ALL ( TableB ),
            __ThisRow >= TableB[Seg 3 Begin]
                && __ThisRow <= TableB[Seg 3 End]
        ),
        TableB[Seg 3 End]
    )
RETURN
    LOOKUPVALUE (
        TableB[Class],
        TableB[Seg 3 End], __maxAcc,
        TableB[Seg 3 Begin], __minAcc
    )

 

Hope this helps

David

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.