Return a specific number based on multiple critera

[danialsj]

I want to create a custom column which will contain data based on multiple conditions. The conditions are given in the rates table.

E.g. the first row of Rates: if product is type A, half year 1, year 2018, in between 0 and 500, then apply 5%.

I want the custom column to look like the Rate column in the Data table. How do I do this?

The following is my data:

Rates

Type	Half Year	Year	Min Amount	Max Amount	Rate
A	1	2018	0	500	5%
A	1	2018	501	1000	10%
B	1	2018	0	500	3%
B	1	2018	501	1000	6%
A	2	2018	0	500	6%
A	2	2018	501	1000	11%
B	2	2018	0	500	4%
B	2	2019	501	1000	10%
A	1	2019	0	500	6%
A	1	2019	501	1000	12%
B	1	2019	0	500	4%
B	1	2019	501	1000	10%
A	2	2019	0	500	6%
A	2	2019	501	1000	12%
B	2	2019	0	500	4%
B	2	2019	501	1000	10%

Data

ID	Type	Half Year	Year	Amount	Rate (Calculated Column)
1001	A	1	2018	100	5%
1002	A	2	2018	500	6%
1003	B	1	2019	250	4%
1004	A	2	2019	1000	12%
1005	B	2	2019	700	10%

[v-xicai · Accepted solution]

Hi @danialsj ,

You can create column like DAX below.

Rate_matched =
MAXX (
    TOPN (
        1,
        FILTER (
            Rates,
            Rates[Type] = Data[Type]
                && Rates[Half Year] = Data[Half Year]
                && Rates[Year] = Data[Year]
                && Rates[Max Amount] >= Data[Amount]
                && Rates[Min Amount] < Data[Amount]
        ),
        Rates[Year] && Rates[Half Year], DESC
    ),
    Rates[Rate]
)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.