Lookup tables with no exact match (find first value greater than)

Question

I’m a beginner with DAX and Power BI. I want to know how you would handle problems like this:

I have two tables; Table A with all my objects and characteristics (e.g. construction year or rental income per month). And Table B a “lookup table” where the boundaries for that class are.

Based on my characteristics, I want to bin/class the building in a calculated column (“Rental class”). So if the rental income is €423 it's under class 1 and if the rental income is €608 it's under class 4

Table A

Building    Construction_year    Rental_income
B1           2016                 €423
B2           1995                 €650
B3           1949                 €720

Table B

Rental_class   Rental_boundary
Under_class_1   €424
Under_class_2   €607
Under_class_3   €651
Under_class_4   €720

This would be my expected result

Building    Construction_year    Rental_income      Rental_class
B1           2016                 €423              Under_class_1
B2           1995                 €650              Under_class_3
B3           1949                 €720              Under_class_4

In Excel, I would use VLOOKUP (TRUE), but in DAX I don’t know how to handle this problem.

This situation happens a lot in my data.

score 0 · Accepted Answer · answered Jun 11 '20 at 14:28

You need to calculate the first class in Table B with boundary greater than the income in Table A.

Similar to this question, we can use TOPN to find the appropriate boundary and then take the class associated with it.

Lookup Rental_class = 
VAR Income = SELECTEDVALUE ( TableA[Rental_income] )
VAR Boundary =
    TOPN (
        1,
        FILTER ( TableB, TableB[Rental_boundary] >= Income ),
        TableB[Rental_boundary], ASC
    )
RETURN
    MAXX ( Boundary, [Rental_class] )

Or you can use a couple CALCULATE functions.

Lookup Rental_class =
VAR Income = SELECTEDVALUE ( TableA[Rental_income] )
VAR Boundary =
    CALCULATE ( MIN ( TableB[Rental_boundary] ), TableB[Rental_boundary] >= Income )
RETURN
    CALCULATE (
        VALUES ( TableB[Rental_class] ),
        TableB[Rental_boundary] = Boundary
    )

Note: If you want to use these for a calculated column, then remove the SELECTEDVALUE wrapper around TableA[Income].

Thank you very much. I've tried both options and they work fine. Thanks — Francesco, Jun 15 '20 at 06:33

Lookup tables with no exact match (find first value greater than)

1 Answers1