Forum Discussion

dokat's avatar
dokat
Post Prodigy
3 years ago
Solved

Dynamic measure based on slicer selection

Hi,

 

I'd like to calculate dynamic measure based on slicer selection. I have 8 values in slicer "Off Invoice Sales,Net Sales, Trade Spend, Volume, Packages, ASP, Gross Profit". Based on slicer selection i would like to execute different measures. For Ex: If Off Invoice Sales is selected i would like to return {Output OIS] measure and so on. I am using measures in stacked column chart 

I tried below dax code however it returns error. See screenshot of the error below. Is there a workaround this issue?

Output Selected Calculation = 
SWITCH(
  SELECTEDVALUE('Output'[P&L]),
   IF(SELECTEDVALUE('Output'[P&L]) = "Off Invoice Sales", CALCULATE([Output OIS])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Net Sales", CALCULATE([Output NS])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Trade Spend", CALCULATE([Output Trade Spend])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Volume", CALCULATE([Output Volume])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Packages", CALCULATE([Output Packages])),
   IF(SELECTEDVALUE('Output'[P&L]) = "ASP",  CALCULATE([Output ASP])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Gross Profit", CALCULATE([Output GP]))
)

 

Need Help
  • Kishore_KVN's avatar
    Kishore_KVN
    3 years ago
    Output Selected Calculation = 
SWITCH(
  TRUE(),
   SELECTEDVALUE('Output'[P&L]) = "Off Invoice Sales", [Output OIS],
   SELECTEDVALUE('Output'[P&L]) = "Net Sales", [Output NS],
   SELECTEDVALUE('Output'[P&L]) = "Trade Spend", [Output Trade Spend],
   SELECTEDVALUE('Output'[P&L]) = "Volume", [Output Volume],
   SELECTEDVALUE('Output'[P&L]) = "Packages", [Output Packages],
   SELECTEDVALUE('Output'[P&L]) = "ASP",  [Output ASP],
   SELECTEDVALUE('Output'[P&L]) = "Gross Profit", [Output GP],
BLANK()
)

     

    Sorry I missed brackets for selectedvalue. Now it will work. 

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

     

4 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    3 years ago

    Hello dokat ,

    No need to use IF and CALCULATE inside a SWITCH statement simply you can mention column name and measure names respectively. Your measure should look as below:

    Output Selected Calculation = 
SWITCH(
  TRUE(),
   SELECTEDVALUE('Output'[P&L] = "Off Invoice Sales", [Output OIS],
   SELECTEDVALUE('Output'[P&L] = "Net Sales", [Output NS],
   SELECTEDVALUE('Output'[P&L] = "Trade Spend", [Output Trade Spend],
   SELECTEDVALUE('Output'[P&L] = "Volume", [Output Volume],
   SELECTEDVALUE('Output'[P&L] = "Packages", [Output Packages],
   SELECTEDVALUE('Output'[P&L] = "ASP",  [Output ASP],
   SELECTEDVALUE('Output'[P&L] = "Gross Profit", [Output GP],
BLANK()
)

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

    • dokat's avatar
      dokat
      Post Prodigy
      3 years ago

      Kishore_KVN Wen i tried your formula its giving me following error message 
      Too many arguments were passed to the SELECTEDVALUE function. The maximum argument count for the function is 2.

      • Kishore_KVN's avatar
        Kishore_KVN
        Solution Sage
        3 years ago
        Output Selected Calculation = 
SWITCH(
  TRUE(),
   SELECTEDVALUE('Output'[P&L]) = "Off Invoice Sales", [Output OIS],
   SELECTEDVALUE('Output'[P&L]) = "Net Sales", [Output NS],
   SELECTEDVALUE('Output'[P&L]) = "Trade Spend", [Output Trade Spend],
   SELECTEDVALUE('Output'[P&L]) = "Volume", [Output Volume],
   SELECTEDVALUE('Output'[P&L]) = "Packages", [Output Packages],
   SELECTEDVALUE('Output'[P&L]) = "ASP",  [Output ASP],
   SELECTEDVALUE('Output'[P&L]) = "Gross Profit", [Output GP],
BLANK()
)

         

        Sorry I missed brackets for selectedvalue. Now it will work. 

        If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!