1 of 1 people found this helpful
Unfortunately, I don't think it's possible to set up conditional formatting of a single measure field being controlled by a parameter. In essence, it appears that your 5 options produce:
2 monetary numbers
If that's the case, the only way to get each to appear correctly is to convert each THEN predicate to a string with the numeric data formatted (and rounded) the way you want.
Another option would be to have your case statement return numeric float values. Then, create 3 different sheets, one with the measure always formatted as a %, one as standard number with comma, etc. and the last with the measure formatted as $.
Then, create calculated fields for each view to use as a filter.
i.e., IF Parameter = 'Conversion Rate' THEN 'Show' ELSE 'Hide' END as the "Percent View Filter"
IF Parameter = 'Men Booked' OR Paremeter = 'Men Who Attended Clinic' THEN 'Show' ELSE 'Hide' END as the "Count View Filter"
IF Parameter = 'Cost (KR)' OR Paremeter = 'Cost per Client (KR)'' THEN 'Show' ELSE 'Hide' END as the "Money View Filter". Apply each filter to the appropriate view and filter to "Show." Then create a dashboard with a vertical container that contains all 3 views, and expose the parameter. Only one view will be shown at a time--the one formatted to match the parameter selection.
If you want to have a title, I'd recommend you create a 4th empty view just for that, and apply none of these 3 filters to it.
Hope that helps. Feel free to attach your workbook if you need assistance.
Thanks for you helpful reply. I see what I need to do and tried to format each of the 5 options (your first suggestion), but am struggling with getting the calculation correct. I think I need to improve my knowledge of the formatting syntax.
Here is my (failed) attempt:
case [Parameters].[Choose Indicator 1]
when 'Conversion Rate' then STR(INT(ROUND([Conversion Rate]*100,0)))+"%"
when 'Men Booked' then STR(LOOKUP(SUM([Men Booked]),0))
when 'Men Who Attended Clinic' then STR(LOOKUP(SUM([Men Who Attended Clinic]),0))
when 'Cost (KR)' then STR(LOOKUP(SUM([Cost (KR)]),0))
when 'Cost per Client (KR)' then STR(INT(ROUND([Cost per Client (KR)]),0))
I've attached the workbook with the functioning (but unformatted) display as well as the above.
Thanks for any further guidance!
I didn't realize you were trying to graph your dynamic measure. Using a string there makes things tougher. You could possibly do it by having one calculated field as your measure and another one as the tooltip, but the axis cannot be changed.
So, if you want to use graphs with the dynamic measure, you'll want to use the 2nd approach. It's not as involved as it sounds, mostly duplicating things and just changing a word in the filter formula.
See attached. Let me know if you have more questions.
parameter-based formatting.twbx.zip 239.1 KB
Thanks a lot for the help! I really appreciate your taking time to lay this out so clearly. This will be useful for me in other instances as well so thanks again!