DAX - FORMAT with SWITCH

Question

I have 2 slicers that are used to allow the end-user to select what measure they want to see.

I am using a SWITCH function to switch between different measures, but I would like to format these as 4 of them are currency and the other 2 are whole numbers. However, when I do this it does not allow me to put the measure into my charts because of the custom formatting:

M_MeasureSelection = 
VAR MeasureSelect = SELECTEDVALUE(MeasureSelection[MeasureType]) & " " & SELECTEDVALUE(MeasureSelection[Measure])

RETURN
SWITCH (
    TRUE (),
    MeasureSelect = "Booked ADR",
        FORMAT([M_BookedADR], "$#.##"),
    MeasureSelect = "Booked Revenue",
        FORMAT([M_BookedRevenue], "$#.##"),
    MeasureSelect = "Booked Room Nights",
        FORMAT([M_BookedRoomNights], "#.##"),
    MeasureSelect = "Actualized ADR",
        FORMAT([M_ActualizedADR], "$#.##"),
    MeasureSelect = "Actualized Revenue",
        FORMAT([M_ActualizedRevenue], "$#.##"),
    MeasureSelect = "Actualized Room Nights",
        FORMAT([M_ActualizedRoomNights], "#.##")
)

It does work with my charts when I take out the FORMAT functions:

M_MeasureSelection2 = 
VAR MeasureSelect = SELECTEDVALUE(MeasureSelection[MeasureType]) & " " & SELECTEDVALUE(MeasureSelection[Measure])

RETURN
SWITCH (
    TRUE (),
    MeasureSelect = "Booked ADR",
        [M_BookedADR],
    MeasureSelect = "Booked Revenue",
        [M_BookedRevenue],
    MeasureSelect = "Booked Room Nights",
        [M_BookedRoomNights],
    MeasureSelect = "Actualized ADR",
        [M_ActualizedADR],
    MeasureSelect = "Actualized Revenue",
        [M_ActualizedRevenue],
    MeasureSelect = "Actualized Room Nights",
        [M_ActualizedRoomNights]
)

Does anyone have any ideas on this? Any other options or do I just have to remove the formatting?

Luis Felipe A. Venezian Luis Felipe A. Venezian · Accepted Answer · 2020-09-25T22:04:59

You could use SWITCHcommand as the code below:

M_MeasureSelection = 
VAR MeasureSelect = CONVERT(SELECTEDVALUE(MeasureSelection[MeasureType]) & " " & SELECTEDVALUE(MeasureSelection[Measure]), STRING)
RETURN
SWITCH (
    MeasureSelect, /* Param */
    "Booked ADR", FORMAT([M_BookedADR],  "Currency"), /* Is Booked ADR? Then ... */
    "Booked Revenue", FORMAT([M_BookedRevenue],  "Currency"), /* Is Booked Revenue? Then ...*/
    "Booked Room Nights", FORMAT([M_BookedRoomNights],  "Currency"),
    "Actualized ADR", FORMAT([M_ActualizedADR],  "Currency"),
    "Actualized Revenue", FORMAT([M_ActualizedRevenue],  "Currency"),
    "Actualized Room Nights", FORMAT([M_ActualizedRoomNights],  "Currency"),
    "Invalid Option!" /* Just for enhance */
)

Make sure that all of your measures inside these FORMAT functions are Numbers and not Strings but if so you could make a CONVERT.

DAX - FORMAT with SWITCH

1 Answers