Dynamic DAX Variable

[fergu513]

Hi,

I have a measure I want to make more dynamic.

Measure = 

VAR ExtSales = ROUND([Ext. Sales],0)

VAR TotalExtSales = ROUND([TotalExtSales (NoPS)],0)

VAR ExtSalesPY = ROUND([Ext. Sales PY],0)

VAR TotalExtSalesPY = ROUND([TotalExtSales PY (NoPS)],0)

VAR ExtGPPY = ROUND([Ext. GP% PY],10)

VAR SelectedRow = SELECTEDVALUE('Add Rows:'[View by: Fields])

// Here's how I would like the next variable to work dynamically:
// VAR TotalGMPY = CALCULATE(ROUND([TotalGM PY],10),REMOVEFILTERS(SelectedRow))
// The line above doesnt  work, so I currently have a hardcoded column in it, but I need that column to be chang-able by the user:

VAR TotalGMPY = CALCULATE(ROUND([TotalGM PY],10),REMOVEFILTERS(PS_TYPE[PS_TYPE]))

Var Mix =

(ExtSales - (TotalExtSales * (ExtSalesPY / TotalExtSalesPY))) * (ExtGPPY - TotalGMPY)

VAR Calc=

SWITCH (

    TRUE(),

    ISINSCOPE(GPH[GPH Level1]),CALCULATE(Mix,ALLSELECTED(GPH),GPH[GPH Level1] IN GPH1),

    ISINSCOPE(GPH[GPH Level2]), CALCULATE(Mix),

    ISINSCOPE(GPH[GPH Level3]), CALCULATE(Mix),

    ISINSCOPE(GPH[GPH Level4]), CALCULATE(Mix),

    ISINSCOPE(Manufacturer[VENDOR_NAME]), CALCULATE(Mix),

    ISINSCOPE(Linebuy[LINEBUY_ID]),CALCULATE(Mix),

    ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(Mix),

    ISINSCOPE(Transactions[PE_BRAND_TYPE]), CALCULATE(Mix),

    ISINSCOPE(Region[EDMCS_LOB_DESC]), CALCULATE(Mix),

    ISINSCOPE(Region[EDMCS_REGION_NAME]), CALCULATE(Mix),

    ISINSCOPE(Linebuy[Commodity.Category1]), CALCULATE(Mix),

    ISINSCOPE(PS_Type[PS_Type]),CALCULATE(Mix,ALLSELECTED(PS_Type)),

    ISINSCOPE(Transactions[BUSINESS_GROUP]),CALCULATE(Mix),

    ISINSCOPE(Transactions[Commodity Tie Out Product Type]), CALCULATE(Mix),

    Total

)

RETURN

Calc

Any ideas how I can make the variable TotalGMPY more dynamic, like the example above that I commented out?

[v-pnaroju-msft]

Hi fergu513,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi fergu513,

Thank you for the update.

Please follow the steps outlined below, which may assist in resolving the issue:

1. DAX cannot track the order of slicer selections; therefore, the best approach is to control behavior based on drill level visibility using ISINSCOPE():
   
   

   VAR TotalGMPY =

       SWITCH(

           TRUE(),

           ISINSCOPE(GPH[GPH Level2]), CALCULATE([TotalGM PY], REMOVEFILTERS(GPH[GPH Level1])),

           ISINSCOPE(GPH[GPH Level1]), CALCULATE([TotalGM PY], REMOVEFILTERS(PS_TYPE[PS_TYPE])),

           ISINSCOPE(PS_TYPE[PS_TYPE]), CALCULATE([TotalGM PY], REMOVEFILTERS(Manufacturer[VENDOR_NAME])),

           CALCULATE([TotalGM PY])

       )
   This measure makes the calculation responsive to the matrix drill level, even when multiple slicer values are selected.

2. The SELECTEDVALUE() function returns a blank if multiple values are selected, which causes the SWITCH() function to fall into the default case. We can rectify this by using ISINSCOPE() or VALUES() instead of SELECTEDVALUE() when supporting multi-selections, or by limiting the slicer to single-select.
   
   

   If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who face similar queries.

   Thank you.

[fergu513]

What if the user selected multiple options from Add Rows and I want the TotalGMPY to change based on what level you are viewing within the matrix?
For example, if they select PS_TYPE, GPH Level 1, and GPH Level 2, in that order: i want TotalGMPY in the GPH Level 2 level to REMOVEFILTERS(GPH Level 1) and then TotalGMPY in the GPH Level 1 level to REMOVEFILTERS(PS_Type). 

How can I make this happen dynamically?

[v-pnaroju-msft]

Hi fergu513,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi fergu513,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @DataNinja777 ,for your response.

Hi @fergu513,

We would like to check if the solution provided by @DataNinja777 has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[DataNinja777]

Hi @fergu513 ,

To make your TotalGMPY variable more dynamic in DAX, since DAX does not allow dynamic column references within REMOVEFILTERS(), you can simulate this behavior by using a SWITCH statement that responds to the user’s selection in the 'Add Rows:'[View by: Fields] slicer. Essentially, you're matching the selected field name to a hardcoded list of column references and applying REMOVEFILTERS() accordingly for each case.

Here's how you can rewrite the TotalGMPY variable:

VAR TotalGMPY =
    SWITCH(
        SelectedRow,
        "PS_TYPE", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(PS_TYPE[PS_TYPE])),
        "Manufacturer", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Manufacturer[VENDOR_NAME])),
        "Linebuy", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Linebuy[LINEBUY_ID])),
        "GPH Level1", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(GPH[GPH Level1])),
        "GPH Level2", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(GPH[GPH Level2])),
        "GPH Level3", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(GPH[GPH Level3])),
        "GPH Level4", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(GPH[GPH Level4])),
        "Region LOB", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Region[EDMCS_LOB_DESC])),
        "Region Name", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Region[EDMCS_REGION_NAME])),
        "Brand Type", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Transactions[PE_BRAND_TYPE])),
        "Business Group", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Transactions[BUSINESS_GROUP])),
        "Commodity Product Type", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Transactions[Commodity Tie Out Product Type])),
        "Commodity Category", CALCULATE(ROUND([TotalGM PY],10), REMOVEFILTERS(Linebuy[Commodity.Category1])),
        CALCULATE(ROUND([TotalGM PY],10))
    )

This approach allows the measure to respond dynamically based on the user's selection in the slicer, effectively emulating dynamic filtering logic. You just need to ensure that the values in 'Add Rows:'[View by: Fields] match the text strings used in the SWITCH statement, or else the default CALCULATE(ROUND([TotalGM PY],10)) will be used. If in the future you want a more scalable setup, you could consider using a disconnected mapping table, but for your current purpose, this method should work cleanly and intuitively.

Best regards,

[fergu513]

Also, it appears that in the varialbe you wrote out above, the default scenario at the bottom is what is selected, despite "PS Type" being selected in the field paramter. It seems the switch function isnt getting activated as planned.
Any idea why the default scenario is the one getting selected?