Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks Matt! That's a pitty.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.