Forum Discussion

Westy85's avatar
Westy85
Frequent Visitor
3 years ago

Format inside Switch losing all filters

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!

1 Reply