Forum Discussion

DataSkills's avatar
DataSkills
Icon for Resolver I rankResolver I
4 years ago
Solved

Using a slicer to dynamically change the value returned by measure

Hi there, 

 

I have a slicer based on a table that I created called "Time Metric" that I inserted with values as shown in the image below. The idea here is that when someone picks eg Month to date, the measure returns Month to date total sales. Previous month shows previous month total sales etc. 

 

 

Dynamic TI Measure = 

IF(
HASONEVALUE(TimeMetric[Metric to display]),
SWITCH(
    VALUES(TimeMetric[Metric to display]),

"Month to date", [Total MTD Sales],
"Previous Month", CALCULATE([xTotal Sales], PREVIOUSMONTH('Calendar'[Date])),
"Previous Year", calculate([xTotal Sales], PREVIOUSYEAR('Calendar'[Date])), 
"Year to date",TOTALYTD([xTotal Sales],'Calendar'[Date])))

 

 

Clearly there is something wrong with my code because it doesn't work! Any suggestions? 

 
  • DataSkills's avatar
    DataSkills
    4 years ago

    Thank you to both Whitewater100  & tamerj1 for your suggestions!

     

    I tried both and  neither worked. Which got me thinking that maybe there was some other problem. So I changed the code to just return an existing measure that I know works and again, I got blanks! Now I knew something other than the code was at the root of the problem!

     

    I then realised I had put a "non-date" dimension into my matrix (Category). Of course, without a date dimension, any time intelligence functions don't work! 🙊🤔

     

     

    Dynamic TI Measure = 
var selection = SELECTEDVALUE(TimeMetric[Metric to display])
Return
SWITCH (
    Selection,
    "Month to date", [Total MTD Sales],
    "Previous Month", CALCULATE ( [xTotal Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) ),
    "Previous Year", CALCULATE ( [xTotal Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ),
    "Year to date", TOTALYTD ( [xTotal Sales], 'Calendar'[Date] )
)

     

     

6 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    HI:

    You can try this:

    Result = 
    var selection = SELECTEDVALUE(TimeMetric[MetrictoDisplay])
    SWITCH(selection), 
     "Month to Date", [Total MTD Sales], 

    "Previous Month", [Prev Month Measure],

    etc

    etc no comma after last one

    )

     

     

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    hi DataSkills 
    You may try

    Dynamic TI Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Month to date", [Total MTD Sales],
    SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Previous Month", CALCULATE ( [xTotal Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) ),
    SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Previous Year", CALCULATE ( [xTotal Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ),
    SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Year to date", TOTALYTD ( [xTotal Sales], 'Calendar'[Date] )
)
  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    I'm sorry that measure didn't work for you. It weird that it did not perform as expected. Probably some minor difference between the models or measures.

    I tested it and it worked fine. I have all my slection measure pre-defined and maybe that's the difference. Anyways I'll post the image of it working. Thank you to too DataSkills for sticking with it.

    New Result =
    var selection = SELECTEDVALUE('Table'[Metric to Display])
    return
    Switch(
    selection,
    "Total Cost", [Total Cost],
    "Total Qty", [Total Qty],
    "Total.Cost.of.Common.Parts", [Total.Cost.of.Common.Parts]
    )

     

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    I forgot to add return..and had an extra )

    New Result = 
    var selection = SELECTEDVALUE(TimeMetric[MetrictoDisplay])

    return
    SWITCH(selection, 
     "Month to Date", [Total MTD Sales], 

    "Previous Month", [Prev Month Measure],

    etc

    etc no comma after last one

    )

    • DataSkills's avatar
      DataSkills
      Icon for Resolver I rankResolver I
      4 years ago

      Tried this but it didn't work. Gives a blank result. 

      Dynamic TI Measure = 

var selection = SELECTEDVALUE(TimeMetric[Metric to display])

return
SWITCH(selection, 
    "Month to date", [Total MTD Sales],
    "Previous Month", CALCULATE([xTotal Sales], PREVIOUSMONTH('Calendar'[Date])),
    "Previous Year", calculate([xTotal Sales], PREVIOUSYEAR('Calendar'[Date])), 
    "Year to date",TOTALYTD([xTotal Sales],'Calendar'[Date]))
  • DataSkills's avatar
    DataSkills
    Icon for Resolver I rankResolver I
    4 years ago

    Thank you to both Whitewater100  & tamerj1 for your suggestions!

     

    I tried both and  neither worked. Which got me thinking that maybe there was some other problem. So I changed the code to just return an existing measure that I know works and again, I got blanks! Now I knew something other than the code was at the root of the problem!

     

    I then realised I had put a "non-date" dimension into my matrix (Category). Of course, without a date dimension, any time intelligence functions don't work! 🙊🤔

     

     

    Dynamic TI Measure = 
var selection = SELECTEDVALUE(TimeMetric[Metric to display])
Return
SWITCH (
    Selection,
    "Month to date", [Total MTD Sales],
    "Previous Month", CALCULATE ( [xTotal Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) ),
    "Previous Year", CALCULATE ( [xTotal Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ),
    "Year to date", TOTALYTD ( [xTotal Sales], 'Calendar'[Date] )
)