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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TaroGulati
Helper II
Helper II

FORMAT Function error

Hi guys,

 

I am having difficulty in using the format function.

 

I have this simple table:

 

TaroGulati_0-1646649354821.png

I am using the simple column chart to represent value based on the slicer selection. 

 

TaroGulati_1-1646649406833.png

This is the measure that i am using:

 

Value = SWITCH(SELECTEDVALUE('Table'[DIM]),
"A",30,
"B",40,
"C",50,
"D",60)
 
my expected result is this, once i select A or B in the slicer it should show value as a whole number but once i select C or D i want it to show as percentage. i tried using Format but it is converting in text & visual is breaking.
 TaroGulati_2-1646649573174.png
TaroGulati_3-1646649670791.png

 

 

any suggestion would be really helpful. 

 

Thanks

 

 

1 ACCEPTED SOLUTION

@TaroGulati 

Please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

9 REPLIES 9
Tahreem24
Super User
Super User

@TaroGulati Try this but it will return as text because it has different return types like whole number and percentage value.

 
Column = SWITCH(TableName[DIM],
"A",Format(30,"##"),
"B",FORMAT(40,"##"),
"C",FORMAT(0.5,"Percent"),
"D",FORMAT(0.6,"Percent")
)
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 thanks for the suggestion. For table is not an issue. I am trying to represnt these values on line chart which doesn't support the text format of measure. 

 

 

amitchandak
Super User
Super User

@TaroGulati , Format the first 2 also, This creates few options as numbers and few as %, that will not work

 

You can explore calculation group if you need a different format for measure slicer

 

tabular - Calculation groups
https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprodu...

@amitchandak  thanks for the suggestion. i believe with the help of calculation group, this would not work because that will also return a measure as text and graph doesn't accept the format text. 

 

Hi @TaroGulati ,

 

Amit is right, take a look at the attached PBIX.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegener thanks for your suggestion. 

 

I think problem is still there. 

 

in your calculation item, you assigned values to each selection of slicer but it is still not showing the values in percentage with % sign on the axis. 

 

i tried to modifiy but still doesn't give the desired result. 

 

Thanks again for your efforts. 

 

Hi @TaroGulati ,

 

for me, the axis changes depending on the selection.

Is your requirement different?

2Format.gif

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegener thank you very much.

 

it seems my power bi desktop is not updated with Feburary 2022 features. Last version is of Dec 2021. I guess for this reason i don't see the same results that you see. 

 

TaroGulati_0-1646667851138.png

Thank you again.

@TaroGulati 

Please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.