DAX - FORMAT with SWITCH

[zfemmer]

I have 2 slicers that are used allow the end-user 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?

[MattAllington]

You could use the technique I describe here to solve this issue. https://exceleratorbi.com.au/dynamic-formatting-of-switch-measures/

[zfemmer]

Do you know if this is even available for Power BI Report Server?

Looks like it was released in July for Power BI Service, but Report Server hasn't had a release since May.

[zfemmer]

This would for sure help me, but we are still on an old version of Power BI Desktop because they have not updated our report server since January.

Maybe this is just another reason to push for it. 🙂