Forum Discussion

SandyBL's avatar
SandyBL
Advocate II
9 years ago
Solved

Lookup value based on value falling within range on other table

Looking to create the "Actual Rate" column in a table based on a rate card table that has the rules on which rate to use for each volume level.  So need help with the Actual Rate column.  I can do th...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    SandyBL

     

     

    ActualRate =
CALCULATE (
    VALUES ( Rates[Rate] ),
    FILTER (
        Rates,
        Rates[Min Volume] <= Volume[Actual Volume]
            && Rates[Max Volume] >= Volume[Actual Volume]
               && Rates[Account]= Volume[Account]
    )
)
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

SandyBL

 

Hello, try this in a calculated column in Table2 (Volume)

 

ActualRate =
CALCULATE (
    VALUES ( Rates[Rate] ),
    FILTER (
        Rates,
        Rates[Min Volume] <= Volume[Actual Volume]
            && Rates[Max Volume] >= Volume[Actual Volume]
    )
)
deepakb18's avatar
deepakb18
Helper I
8 years ago

 

If I need to write the same query logic in while add custom column in Edit Queries Then how we can write ?