How to lookup Grades corresponding to Marks scored by students from a Grade-Slab table in Power BI?

Question

This is the raw data table that contains marks against the subjects for each students.

There is another table 'Grade_slab' that contains details regarding the marks & grades from which I need to lookup the grades: enter image description here

I need to perform a similar function like Vlookup('marks','Grade_slab'A1:C10,1) (approximate +1 value). Any suggestion?

enter image description here

Right now I have dealt with this situation using the switch function. How to do this using a proper lookupvalue function in Power BI.

Answer 1

this is the sample data

Table

Table(2)

then you can create a column

Column=
VAR _mark =
    MAXX (
        FILTER (
            'Table (2)',
            'Table (2)'[subject] = 'Table'[Subject]
                && 'Table (2)'[Marks] <= 'Table'[marks]
        ),
        'Table (2)'[Marks]
    )
RETURN
    MAXX (
        FILTER (
            'Table (2)',
            'Table (2)'[subject] = 'Table'[Subject]
                && 'Table (2)'[Marks] = _mark
        ),
        'Table (2)'[Grade]
    )