Forum Discussion
Metric Switch Formatting Error
I have two metric switch statements. One switch returns the current week data, the other switch is the prior 6 week data. I need to create a Varience between the two, so I need to subtract the two metric switch statements. The issue is that in both metric switch statements, there are 2 measures that need to be formatted as % and I receive an Error: "Cannot convert value '%' of type Text to type numeric/date. I am only formatting the measures to % in the metric switch statements, since metric switchs change data type to default. (Images Below)
Note: The formatting of the % works properly in each individual metric switch, just not when I subtract the switches together.
I am not sure why I am getting this error when the formatting is the same in the two switch statements. I have been able to subtract metric switch statements in other meausres to get Var, but they didnt have formatting on them.
Is there a workaround for this issue or am I just missing something? Thanks in advance!
I just figured out a way to resolve it after trying every possible way. I was able to create a third metric switch statement that did the subtraction and formatting in the last metric switch used. Simple = Difficult haha
Thank you for all your help and suggestions! Radhika2605 Fowmy
9 Replies
Anonymous
It the data type issue in FORMAT function
Check the cancellation and Productivity measures, are then text ?. use them without FORMAT function.Fowmy Thanks for the response!
Since Metric Switch turn all measures in it to default format "text", I have left Cancellation % and Production % format as "General" in the original meausre. The only place I format them to % is in the Metric Switch. Even if I changed the format in the original measure, the switch statement removes it and turns it to text.Anonymous
When you use FORMAT, the result becomes text, you cannot do calculations on that.
Hi Anonymous ,
I believe the issue is because the result of Format function is a String and when you are subtracting switch then you are for some values subtracting string from string which is not possible.
Is it possible for you to multiply the result of calculation in measures like Cancellation %, Productivity % by 100 and format then using options on the Column Toold Ribbon from the top to say that it is a % instead of using Format function in switch?
Could you please mark this as a solution if this answers your query.
Thanks
Radhika
Anonymous Try to enclose your Swtich statement within Value() like -
Value( Switch () )
Value will convert text to string and then you should be able to subtract it.
Could you please mark this as a solution if this answers your query.
Thanks
Radhika
Radhika2605
I am still getting the error when I wrap my switch statements in Value(Switch() ).I am trying out your other suggestion
Hi Anonymous If my second option (Is it possible for you to multiply the result of calculation in measures like Cancellation %, Productivity % by 100 and format then using options on the Column Toold Ribbon from the top to say that it is a % instead of using Format function in switch?) doesnt work then we can try to do these things-
1. Actually what I meant was as you mentioned that switch is converting the data type of result to text so you can do following -Value(Switch()) - Value(Swicth) //in your measure where you are subtracting two switches.
2. If the above doesnt work then can you please try to use value function outside of the Format functions so that as format is returning a text then it will be converted to Number (not entrierly sure if this will work though).
3. to check if switch is converting value to text - I would suggest to do a test. Remove the format functions so that the result of Cancellation, Productivity and all other measures is number (check the data types too before using them in switch) and then try to subtract it. In this way at least we will know if Switch is causing a problem or Format and can then see what can be done.
Thanks
Radhika
hope this clarifies.
