FORMAT function with standard string and 1 decimal digit

[kyrpav]

I have a pivot table and for the calculation of the values i am using a switch statement. For each different row i have a different calculation.

Code of each row is like this:

SWITCH(SELECTEDVALUE(v_raw_data[metric_code]),
     "T_01",FORMAT([T_01],SELECTEDVALUE(v_raw_data[value_formatter])),
     BLANK())

In the value_formatter i am using the standard string list.

PREDEFINED NUMERIC FORMATS Format Description

"General Number"	Displays number with no thousand separators.
"Currency"	Displays number with thousand separators, if appropriate; displays two digits to the right of the decimal separator. Output is based on system locale settings.
"Fixed"	Displays at least one digit to the left and two digits to the right of the decimal separator.
"Standard"	Displays number with thousand separators, at least one digit to the left and two digits to the right of the decimal separator.
"Percent"	Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator.
"Scientific"	Uses standard scientific notation, providing two significant digits.
"Yes/No"	Displays No if number is 0; otherwise, displays Yes.
"True/False"	Displays False if number is 0; otherwise, displays True.
"On/Off"	Displays Off if number is 0; otherwise, displays On.

In the way that this works if decimals exist then i always get 2 decimals. Else i have integers. I am giving you also i picture

How can i change it to give me if we have decimals only one. But not all values to have one decimal.

In another way if i have these two function:

1) FORMAT ( 5, "0.#" ) 
2) FORMAT ( 0.56, "0.#" ) 

How can i succeed with a result of 

1) 5
2) 0.6

with the same String format. 

[OwenAuger]

Hi @kyrpav 

I don't believe there is any solution involving a single format string that will show integers with no decimal point/places, but otherwise show a decimal point & one decimal place.

I would say the other options are either:

1. Add an additional column to your v_raw_data table that specifies format strings for integers, e.g. add a column v_raw_data[value_formatter_integer].
Then change your DAX expression so that it uses this new column for integers. Something like this (with a bit of re-jigging as well):

VAR MetricValue = 
    SWITCH (
        SELECTEDVALUE( v_raw_data[metric_code] ),
        "T_01", [T_01],
        //...
        BLANK ()
    )
VAR FormatString = 
    IF (
        MetricValue = TRUNC ( MetricValue ), -- integer
        SELECTEDVALUE ( v_raw_data[value_formatter_integer] ),
        SELECTEDVALUE ( v_raw_data[value_formatter] )
    )
RETURN
    FORMAT ( MetricValue, FormatString )

2. Alternatively, you could do something with calculation group format strings.

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

Regards,

Owen

[kyrpav]

I was thinking the same thing if i understand well to do it like

switch(selectedvalue(v_raw_data[metric_code]), 
"T_02",if(TRUNC([T_02])=[T_02],FORMAT([T_02],SELECTEDVALUE(v_raw_data[value_formatter])),FORMAT([T_02],"##0.0")) 

but i did not like to run the same function too many times. What you propose to split it also i think is better.

[OwenAuger · Accepted solution]

Yes that's the idea.

I would suggest you use variables to avoid evaluating the same measure(s) multiple times.

The code I posted earlier was what I was thinking as far as structure:

• Store the measure itself in one variable MeasureValue (T_01, T02 etc).
• Then store the chosen format string in a second variable
• Finally, format the measure using the format string