How to concatenate TEXT and a measure without losing the formatting of measure value.

[AaronToth]

Hi,
I have about 3 different variations of where I am putting text on a report that are not showing up how I would have expected or hoped.

I have a 

measure that calculates hourly wage.  That value is $24.96 (Decimal type, with currency formatting)

measure that calcuates a total items sold based on single column.  The number is 7,300,150 (data type is whole number with "," on)

measure that calcuates the max date of a column.  The value is May, 2023 (data type is date and format is mmm, yyyy)

When I use either CONCATENATE or simply a '&' to join any one of these 3 measures above with some text, the following happens to my measures

$24.96 becomes 24.9283974298374239

7,300,150 becomes 7300150

May, 2023 becomes 05/01/2023

How can I join these measures of specific types (i.e. double, currency, whole number...etc.) with some text so that I don't lose that formatting?  I don't want to use custom HTML widgets if possible.

Thanks!

[AaronToth]

@OwenAuger - Thanks for this.  I was looking at how to this with option #1 in mind like you listed above.  So far I have done 2 of them using option #1.  

[OwenAuger · Accepted solution]

Hi @AaronToth

As you've observed, when numerical values are cast as text (e.g. by concatenating with other strings) a default format will be applied, which is usually not what you want.

In order to control the format, here are some options for you, with a sample PBIX attached.

1. Write additional measures that return text values using FORMAT ( <measure>, <format> )
   This requires restating the format string within these measures.
2. Create a calculation group with a calculation item that makes use of
   FORMAT ( SELECTEDMEASURE(), SELECTEDMEASUREFORMATSTRING() )
   concatentated with other text as required.
   Then apply this calculation group to a visual displaying any of the original measures.
3. Create the calculation group as in step 2, but apply the calculation item as a filter within CALCULATE within another measures.

The DAX for each of these methods in my sample PBIX (in Tabular Editor DAX Script format)

1. Measures that apply the FORMAT function

-------------------------------------
-- Measure: [Hourly Wage Text Simple]
-------------------------------------
MEASURE Dummy[Hourly Wage Text Simple] = "Hourly Wage: " & FORMAT ( [Hourly Wage], "\$#,0.###############;(\$#,0.###############);\$#,0.###############")

----------------------------------
-- Measure: [Max Date Text Simple]
----------------------------------
MEASURE Dummy[Max Date Text Simple] = "Max Date: " & FORMAT ( [Max Date], "mmm, yyyy")

------------------------------------------
-- Measure: [Total Items Sold Text Simple]
------------------------------------------
MEASURE Dummy[Total Items Sold Text Simple] = "Total Items Sold: " & FORMAT ( [Total Items Sold], "#,0")

2.  Calculation group that can be applied to any measure and references the measure's existing format string

-------------------------------------------------------
-- Calculation Group: 'Text Measures Calculation Group'
-------------------------------------------------------
CALCULATIONGROUP 'Text Measures Calculation Group'[Text Measure Option]

    CALCULATIONITEM "Measure to Text" = 
        -- Converts measure to text using its own format string
        FORMAT (
            SELECTEDMEASURE (),
            SELECTEDMEASUREFORMATSTRING ()
        )

    CALCULATIONITEM "Measure to Text with measure name" = 
        -- Converts measure to text using its own format string
        SELECTEDMEASURENAME ( ) & ": "
            & FORMAT (
                SELECTEDMEASURE ( ),
                SELECTEDMEASUREFORMATSTRING ( )
            )

3. Measure that applies the calculation item as a filter with CALCULATE

---------------------------------------------------------------
-- Measure: [Multiple measures formatted via calc group filter]
---------------------------------------------------------------
MEASURE Dummy[Multiple measures formatted via calc group filter] = 
    VAR HourlyWageFormatted =
        CALCULATE (
            [Hourly Wage],
            'Text Measures Calculation Group'[Text Measure Option]
                = "Measure to Text"
        )
    VAR MaxDateFormatted =
        CALCULATE (
            [Max Date],
            'Text Measures Calculation Group'[Text Measure Option]
                = "Measure to Text"
        )
    VAR ItemsSoldFormatted =
        CALCULATE (
            [Total Items Sold],
            'Text Measures Calculation Group'[Text Measure Option]
                = "Measure to Text"
        )
    VAR Result =
        "Hourly Wage: " & HourlyWageFormatted
            & "| Max Date: "
            & MaxDateFormatted
    
            & "| Total Items Sold: "
            & ItemsSoldFormatted
    RETURN
        Result

Do any of these methods work for you?

Please post back if needed 🙂

Regards,

Owen