Add custom column with multiple criteria

johnphil You need to have bit transforming to your lookup table before you use that. This can be achieved in the "Power Query Editor" as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUjYpS0lEyVIrViVYyNtA1tgTyjMA8AwNdI0sdEwNdS5CYsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BuType = _t, Identifier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BuType", type text}, {"Identifier", Int64.Type}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"BuType", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "BuType"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"BuType", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "BuType", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"BuType.1", "BuType.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"BuType.1", type text}, {"BuType.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"BuType.1", "BuTypeLower"}, {"BuType.2", "BuTypeHigher"}})
in
    #"Renamed Columns"

Now it will look like this...

Then, you can use this lookup table (Which was transformed as above) in your main table as below

Column = CALCULATE(VALUES(Test30Lkp[Identifier]),FILTER(Test30Lkp,Test30CalcField[BUType]>=Test30Lkp[BuTypeLower] && Test30CalcField[BUType] <= Test30Lkp[BuTypeHigher]))

Here is the expected output...