Forum Discussion
Dynamic DAX Variable
Hi fergu513,
Thank you for the update.
Please follow the steps outlined below, which may assist in resolving the issue:
-
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. -
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.
-
Hi Anonymous ,
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,
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?