FORMAT Function error

[TaroGulati]

Hi guys,

I am having difficulty in using the format function.

I have this simple table:

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

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.

 

any suggestion would be really helpful. 

Thanks

[Tahreem24]

@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")

)

[TaroGulati]

@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]

@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...

[TaroGulati]

@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. 

[mwegener]

Hi @TaroGulati ,

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

[TaroGulati]

@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. 

[mwegener]

Hi @TaroGulati ,

for me, the axis changes depending on the selection.

Is your requirement different?

[TaroGulati]

@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. 

Thank you again.

[mwegener · Accepted solution]

@TaroGulati 

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