Format function misbehaving. How to achieve the format that I need?

[Wieuwzak]

Hi,

I have a dataset and want to use the format function to apply custom format to a measure. However, when that measure is used in visuals, that measure no longer handles relationships. A measure without the format formula works fine. My goal is to format currency to nl-NL locale and display that format in visuals.

The format displays correctly in Power query, but not in visuals. It can be displayed correctly using the format function (see Formatted measure), but then the relationships are no longer acknowledged.

I've tried every possible setting regarding regions and locale but all tries have failed to achieve this other than using the format function.

Normale measure: Measure = sum(DATA_Budgets[Amount])

Formatted measure: formatted measure = format(SUM(DATA_Budgets[Amount]),"Currency","nl-NL")

Please see pictures. How can I overcome this issue? Thanks in advance for advice!

[AlexisOlson · Accepted solution]

The format applied in Power Query does not direcly affect the format in the report.

In general, I'd recommend using formatting strings on your measures rather than using FORMAT to convert your measure to output a string. FORMAT does not affect the model relationships but it will make a blank return an empty string, so you need to account for that. For example,

formatted =
VAR _Value = SUM ( DATA_Budgets[Amount] )
RETURN
    IF (
        NOT ISBLANK ( _Value ),
        FORMAT ( _Value, "Currency", "nl-NL" )
    )

[Wieuwzak]

Thank you. This was the solution!

[lbendlin]

Refactor your data model (denormalize the department and group dimensions)

Read the blog entry (and watch the video) for the August 2024 announcement on the new Formatting philosophy.