Puede usar Power Query para transformar la tabla de criterios en una lista de productos coincidentes, p. ej.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUQpJLEpPLVHwS8xNVTAE8j1LUnMV3IvySwtQOAgFISE+WMhYHVyGGgH5zoklqen5YCkYE2FgcGlSMkIeiYdQ4lSUmJcCo2HCuK00JmyqEV6fuKUmFeEIH5xeIctEI0IhbkTYKybEWmaMIyTNcbs8FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Target = _t, Criteria_1 = _t, Value_1 = _t, Criteria_2 = _t, Value_2 = _t, Criteria_3 = _t, Value_3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Target", type text}, {"Criteria_1", type text}, {"Value_1", type text}, {"Criteria_2", type text}, {"Value_2", type text}, {"Criteria_3", type text}, {"Value_3", type text}}),
Prods = List.Buffer( Table.ToRecords( Products)),
AddedList = Table.AddColumn( #"Changed Type", "Prod Names", each
let
Criteria1Col = [Criteria_1],
Criteria1Val = [Value_1],
Criteria2Col = [Criteria_2],
Criteria2Val = [Value_2],
Criteria3Col = [Criteria_3],
Criteria3Val = [Value_3],
MatchingProds = List.Select( Prods, each
( Criteria1Col = "TTL" or Record.Field( _, Criteria1Col) = Criteria1Val) and
( Criteria2Col = "TTL" or Record.Field( _, Criteria2Col) = Criteria2Val) and
( Criteria3Col = "TTL" or Record.Field( _, Criteria3Col) = Criteria3Val)
)
in
List.Transform( MatchingProds, each [Product])
, type list),
#"Removed Columns" = Table.RemoveColumns(AddedList,{"Criteria_1", "Value_1", "Criteria_2", "Value_2", "Criteria_3", "Value_3"}),
#"Expanded Prod Names" = Table.ExpandListColumn(#"Removed Columns", "Prod Names"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Prod Names",{{"Prod Names", type text}})
in
#"Changed Type1"
A continuación, puede utilizar TREATAS para hacer coincidir los productos de un determinado objetivo / mes con la tabla de productos, p. ej.
Sales for Target 1 =
VAR MatchingProducts =
CALCULATETABLE (
VALUES ( Targets[Product] ),
TREATAS ( VALUES ( 'Date'[Month] ), Targets[Month] ),
Targets[Target] = "Target 1"
)
VAR Result =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( TREATAS ( MatchingProducts, Products[Product] ) )
)
RETURN
Result
