DAX - FORMAT with SWITCH

zfemmer — Fri, 25 Sep 2020 19:12:39 GMT

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?

Re: DAX - FORMAT with SWITCH

MattAllington — Fri, 25 Sep 2020 21:46:19 GMT

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

Re: DAX - FORMAT with SWITCH

zfemmer — Mon, 28 Sep 2020 17:01:49 GMT

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. 🙂

Re: DAX - FORMAT with SWITCH

zfemmer — Mon, 28 Sep 2020 17:08:37 GMT

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.

topic DAX - FORMAT with SWITCH in DAX Commands and Tips

DAX - FORMAT with SWITCH

Re: DAX - FORMAT with SWITCH

Re: DAX - FORMAT with SWITCH

Re: DAX - FORMAT with SWITCH