Dynamically change the referenced column in a DAX Measure or in Merging Queries

[elia22gr]

Hi team,

I have the below case. I have specific targets (Target Name 1, Target Name 2 etc) which each month have changes in criterias. For example on January the Target Name 1 has a single criteria bases on Item Group column of Dim_Products where eligible are only the products of Item Group Name 1, the Target Name 2 has 3 Criterias, the eligible products are those that are in Category Name 1 (Dim Products coulumn Category), and Subcategory Name 1  (Dim Products coulumn Subcategory) and Brand Name 1 (Dim Products coulumn Brand), the Target Name 3 has 1 criteria the products that have Subcategory Name 2 (Dim Products coulumn Subcategory). On February the same targets have different criterias. Target Name 1 has 1 criteria, Products that have Category Name 2 (Dim Products coulumn Category), Target Name 2 has 2 criterias, products that have Item Group Name 2 (Dim Products coulumn Item Group) and Subcategory Name 4 (Dim Products coulumn Subcategory), andTarget Name 3 has 1 criteria Products with Brand Name 17 (Dim Products coulumn Brand).

Targets Settings Table

Month	Target	Criteria_1	Value_1	Criteria_2	Value_2	Criteria_3	Value_3
January	Target Name 1	Item Group	Item Group Name 1	TTL	TTL	TTL	TTL
January	Target Name 2	Category	Category Name 1	Subcategory	Subcategory Name 1	Brand	Brand Name 1
January	Target Name 3	Subcategory	Subcategory Name 2	TTL	TTL	TTL	TTL
February	Target Name 1	Category	Category Name 2	TTL	TTL	TTL	TTL
February	Target Name 2	Item Group	Item Group Name 2	Subcategory	Subcategory Name 4	TTL	TTL
February	Target Name 3	Brand	Brand Name 17	TTL	TTL	TTL	TTL

In Criterias Columns always refering a column from Dim Products Table and on Valuses columns a value from this column

Dim Products Table

Product	Item Group	Category	Subcategory	Brand
Product 1	Item Group Name 1	Category Name 1	Subcategory Name 1	Brand Name 1
Product 2	Item Group Name 2	Category Name 3	Subcategory Name 4	Brand Name 4
Product 3	Item Group Name 3	Category Name 5	Subcategory Name 8	Brand Name 9
Product 4	Item Group Name 3	Category Name 6	Subcategory Name 9	Brand Name 11
Product 5	Item Group Name 1	Category Name 2	Subcategory Name 3	Brand Name 3
Product 6	Item Group Name 4	Category Name 8	Subcategory Name 11	Brand Name 13
Product 7	Item Group Name 3	Category Name 5	Subcategory Name 7	Brand Name 8
Product 8	Item Group Name 5	Category Name 10	Subcategory Name 14	Brand Name 16
Product 9	Item Group Name 1	Category Name 1	Subcategory Name 1	Brand Name 1
Product 10	Item Group Name 5	Category Name 11	Subcategory Name 15	Brand Name 19
Product 11	Item Group Name 3	Category Name 7	Subcategory Name 10	Brand Name 12
Product 12	Item Group Name 5	Category Name 12	Subcategory Name 16	Brand Name 20
Product 13	Item Group Name 1	Category Name 1	Subcategory Name 2	Brand Name 2
Product 14	Item Group Name 4	Category Name 9	Subcategory Name 13	Brand Name 15
Product 15	Item Group Name 3	Category Name 5	Subcategory Name 8	Brand Name 10
Product 16	Item Group Name 5	Category Name 11	Subcategory Name 15	Brand Name 18
Product 17	Item Group Name 2	Category Name 3	Subcategory Name 4	Brand Name 5
Product 18	Item Group Name 5	Category Name 10	Subcategory Name 14	Brand Name 17
Product 19	Item Group Name 3	Category Name 6	Subcategory Name 9	Brand Name 11
Product 20	Item Group Name 4	Category Name 8	Subcategory Name 12	Brand Name 14
Product 21	Item Group Name 2	Category Name 4	Subcategory Name 5	Brand Name 6
Product 22	Item Group Name 2	Category Name 4	Subcategory Name 6	Brand Name 7

How can I manage it to filter the sales (fact Table) as it is set up by target? Actual vs Target Name 1, Actuals vs Target Name 2 etc?

Notice that each month each target may change the criteria (so as the column needs to be filtered out of the Dim Products table).

I need either an approch for Power Query or DAX Measure.

Thank you in advance,

Eleftheria Koniari

[johnt75]

You can use Power Query to to transform the criteria table into a list of matching products, e.g.

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"

You could then use TREATAS to match the products for a given target / month to the products table, e.g.

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

[v-hjannapu]

Hi @elia22gr,

I would also take a moment to thank @johnt75 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you,

Harshitha.

Community Support Team.