Combine FORMAT() and SWITCH()

[sguenther]

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

[Anonymous]

Hello,

Is there any updates for this question? I have exactly same question. 

Thank you,

Jarvis

[MattAllington · Accepted solution]

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

[sguenther]

Thanks Matt! That's a pitty.