Forum Discussion
Combine FORMAT() and SWITCH()
Hey guys,
quick DAX question: I would like to create a really interactive report for my colleagues and one issue I couldn't solve so far was the following. I created a Slicer for "relative" and "absolute" numbers. Based on which option is ticked, I would like to calculate different things in a measure. So for "absolute" an absolute number is calculated and for "relative" a ratio is calculated relative to a base value.
The measure looks like the following:
Measure = SWITCH( IF( HASONEVALUE(slicer_relative_absolute[Absolute/Relative]), VALUES(slicer_relative_absolute[Absolute/Relative]), BLANK() ), "absolute", [absolute number] , "relative", [absolute number]/[base] , BLANK() )
what I would like to do now is to format the relative number as percentage. But if I change it in the measure it doesn't return anything using the following code:
Measure = SWITCH( IF( HASONEVALUE(slicer_relative_absolute[Absolute/Relative]), VALUES(slicer_relative_absolute[Absolute/Relative]), BLANK() ), "absolute", [absolute number] , "relative", FORMAT([absolute number]/[base], "Percent") , BLANK() )
Anybody got an idea how to fix it?
Thanks and best,
Sebastian
Yes I know - it can't be done if you want to have it as a number. You can add a format (effectively turning it into text) if you want, but then it wont work on charts etc. It is an unfortunate limitation that you can't switch formats
3 Replies
- MattAllingtonCommunity Champion
Yes I know - it can't be done if you want to have it as a number. You can add a format (effectively turning it into text) if you want, but then it wont work on charts etc. It is an unfortunate limitation that you can't switch formats
- sguentherAdvocate II
Thanks Matt! That's a pitty.
- AnonymousNot applicable
Hello,
Is there any updates for this question? I have exactly same question.
Thank you,
Jarvis