Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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
@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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
51 | |
47 | |
16 | |
13 |