Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic DAX Variable

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],...
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    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.


       

v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

Thank you, DataNinja777 ,for your response.

 

Hi Anonymous,

 

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.