Format inside Switch losing all filters

[Westy85]

Hi,

I am very much a self taught noob at PowerBI and DAX, but have managed to build a basic P/L report that combines various measures via the switch operator to display a single column of data for a specific date

The full switch measure is below

Values to Display = 
   VAR DisplayItem = SELECTEDVALUE('Dim_PnL_Fields'[SubCategory])
   VAR DisplayItem2 = SELECTEDVALUE('Dim_PnL_Fields'[Category])
   RETURN
     SWITCH (
        TRUE (),
        DisplayItem = "Restaurant - Beverage Sales", [Total Beverage Sales],
        DisplayItem = "Restaurant - Food Sales", [Total Food Sales],
        DisplayItem = "FOH Wages", [Total FoH Wages],
        DisplayItem = "Kitchen Wages", [Total BoH Wages],
        DisplayItem = "No of Covers", [Total Customers],
        DisplayItem = "FOH Wage % of Total Restaurant Rev", ([Total FoH Wages]/[Total Sales])*100,
        DisplayItem = "Kitchen Wages % of Food Revenue", ([Total BoH Wages]/[Total Food Sales])*100,
        DisplayItem = "Total Salaries and Wages", [Total Wages],
        DisplayItem = "Payroll Related Expenses", [Wage Expenses],
        DisplayItem = "Discounts", [Total Discounts],
        DisplayItem = "COS - Beverage", [Total Beverage Cost],
        DisplayItem = "COS - Food", [Total Food Cost],
        DisplayItem = "Total COS", [Total Costs],
        DisplayItem = "COS % of Revenue", [COS % of Revenue],
        DisplayItem = "Other Operating Expenses & Admin Expenses", [Other Expenses],
        DisplayItem = "Net Profit/(Loss)", [Profit/Loss],
        DisplayItem = "Net Profit/(Loss)%", [Profit/Loss Per],
        DisplayItem2 = "Total Revenue", [Total Sales], 
        DisplayItem = "Total Wage %", (([Wage Expenses]+[Total Wages])/[Total Sales])*100
     )

 

the next step is to format each value as either currency, % or whole number. I have found various threads that state to use a format tag around the measure being switched in.

However, as soon as I do that my matrix loses all of my filters.

Below is the updated DAX with a format for one measure, and what happens to my matrix

Values to Display = 
   VAR DisplayItem = SELECTEDVALUE('Dim_PnL_Fields'[SubCategory])
   VAR DisplayItem2 = SELECTEDVALUE('Dim_PnL_Fields'[Category])
   RETURN
     SWITCH (
        TRUE (),
        DisplayItem = "Restaurant - Beverage Sales", Format([Total Beverage Sales], "$#.##"),
        DisplayItem = "Restaurant - Food Sales", [Total Food Sales],
        DisplayItem = "FOH Wages", [Total FoH Wages],
        DisplayItem = "Kitchen Wages", [Total BoH Wages],
        DisplayItem = "No of Covers", [Total Customers],
        DisplayItem = "FOH Wage % of Total Restaurant Rev", ([Total FoH Wages]/[Total Sales])*100,
        DisplayItem = "Kitchen Wages % of Food Revenue", ([Total BoH Wages]/[Total Food Sales])*100,
        DisplayItem = "Total Salaries and Wages", [Total Wages],
        DisplayItem = "Payroll Related Expenses", [Wage Expenses],
        DisplayItem = "Discounts", [Total Discounts],
        DisplayItem = "COS - Beverage", [Total Beverage Cost],
        DisplayItem = "COS - Food", [Total Food Cost],
        DisplayItem = "Total COS", [Total Costs],
        DisplayItem = "COS % of Revenue", [COS % of Revenue],
        DisplayItem = "Other Operating Expenses & Admin Expenses", [Other Expenses],
        DisplayItem = "Net Profit/(Loss)", [Profit/Loss],
        DisplayItem = "Net Profit/(Loss)%", [Profit/Loss Per],
        DisplayItem2 = "Total Revenue", [Total Sales], 
        DisplayItem = "Total Wage %", (([Wage Expenses]+[Total Wages])/[Total Sales])*100
     )

In this example I have only formatted the first measure for testing -  Format([Total Beverage Sales], "$#.##").

But as soon as I save/validate the measure the report loses all values and seems to ignore my slicers.

This is probably something silly that I have missed in my DAX, but I cant for the life of me figure it out.

Thanks in advance!

[amitchandak]

@Westy85 , Check if field parameters can help then use that

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf