cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sguenther
Advocate II
Advocate II

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

1 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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3
jarvisli
Regular Visitor

Hello,

 

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

 

Thank you,

 

Jarvis

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Thanks Matt! That's a pitty.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors