Forum Discussion

sguenther's avatar
sguenther
Advocate II
10 years ago
Solved

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

  • MattAllington's avatar
    MattAllington
    Community 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

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hello,

     

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

     

    Thank you,

     

    Jarvis