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!
Hierarchy Not Rolling Up As expected
Hi All, I am using a switch function to sum up values in a Power BI matrix, but the hierarchy is not rolling up as I expected it to. I am using a switch function to flip between the value I want for France and the values for the rest of countries. Here is my DAX: Test Rollup = switch(SELECTEDVALUE('Hierarchy'[level2]), "FRANCE", sum('Table'[Amount1])/1000, sum('Table'[Amount2] )/1000) The Matrix seems to be only rolling up the values for the second argument in the switch function. I would the total to be 1,602.57 as France should be zero, but it is adding the value for Amount2 instead of Amount1. Does anyone know why this is? Please see image below and let me know if there are any questions.
DAX Switch Formula Failing to Load in Waterfall Chart
Hello All, Looking for assistance on a DAX formula that is seriously limiting my ability to build a report. The report centres on a Waterfall chart that is fed by a SWITCH formula, to allow for multiple measures to show the variances between two datasets (image below). The measure that feeds the chart is below: NS Waterfall Value = SWITCH ( VALUES ( 'NS Account Table'[Index] ), 1, [Vol/Mix Net Sales]/1000, 2, [Rate xFX Gross Sales]/1000, 3, [Rate xFX Promo Allowance]/1000, 4, [Rate xFX Other Trade]/1000, 5, [Rate xFX Slotting]/1000, 6, [Rate xFX Coupons]/1000, 7, [Rate xFX Cash Disc. and Unsaleable]/1000, BLANK()) The above is set up in a manner, in that an unconnected table is used as the category option for the waterfall chart, and then used in a switch formula to then sub in the individual measures into the points of the waterfall chart below. All measures are set up in the same way, with an example below: Calculate('Combined P&L'[Rate xFX],Account[Sub-Account]="Customer Sales")+Calculate('Combined P&L'[Rate xFX],Account[Sub-Account]="CPU Allowance") The Rate xFX measure being used, is another measure however that runs in 700-1000 ms, thus I have assumed this is not the issue. Stand alone with no filters, the above switch function can run up to 15 seconds, and when filtering for different characteristics, that can run up to a full minute, or even crash with the message stating "Not enough memory to complete this operation". Any help would be much appreciated, and feel free to let me know if any other data is needed.Switch definition of Calculated Column based on slicer
I am using an Entity Column as the primary axis for multiple reports in PowerBI. I have a requirement where I need to turn this into a calculated column, that takes its value from one of two columns. The decision on which column to use needs to be powered by a slicer. Currently, I have set up a "slicer" table that is simply as seen below: Column Yes No I then create a Calculated Column with the following definition - Column = IF( HASONEVALUE('Table'[Value]), SWITCH( VALUES('Table'[Value]), "Yes",A[Name1], "No",A[Name2])) My hope is that with the above definition, I should be able to select either "Yes" or "No" in the slicer to change the definition of the calculated column to either Name1 or Name2. The outcome would be that based on my selection of Yes or No, the entire report would switch to using Name1 or Name2 as the axis in its visualizations. Currently, this calculated column simply returns a blank value. How can I modify this DAX to achieve my intended results? If not possible, are there any other options to achieve the same? ThanksMetric Switch Formatting Error
I have two metric switch statements. One switch returns the current week data, the other switch is the prior 6 week data. I need to create a Varience between the two, so I need to subtract the two metric switch statements. The issue is that in both metric switch statements, there are 2 measures that need to be formatted as % and I receive an Error: "Cannot convert value '%' of type Text to type numeric/date. I am only formatting the measures to % in the metric switch statements, since metric switchs change data type to default. (Images Below) Note: The formatting of the % works properly in each individual metric switch, just not when I subtract the switches together. I am not sure why I am getting this error when the formatting is the same in the two switch statements. I have been able to subtract metric switch statements in other meausres to get Var, but they didnt have formatting on them. Is there a workaround for this issue or am I just missing something? Thanks in advance!
Switch colour card filter by field value
I'm formating a card by FIELD VALUE Is there a way to get this switch formula to turn greater than 0 green e.g. "#094" and red if less than 0 e.g. "#d64550"; My current code is: Measure Value Color = SWITCH(TRUE(), [% Average 2019] >0,"#D64550", [%Average 2019] =0,"#252423", [% Average 2019] <0,"#094", [% Average 2020] >0,"#D64550", [% Average 2020] =0,"#252423", [% Average 2020] <0,"#094" ) It keeps turning red for some reason when it's +18.54% ThanksConditional Formatting on a Switch Metric - Tabular Model
Hello, I am attempting to use the Conditional Formatting - Field Value functionality on a switch metric for a Card visualization; however, the field selector does not let me choose a field value from my model. The "OK" button is grayed out for every metric from my model. The switch metrics are all functioning properly if I view the data in a normal table format with the coloring metric accurately displaying the Color metric as "Red", "Green", "Yellow", etc... as a text field in the table. The data model was published through visual studio to a serve and the PowerBI file is connected to the server hosting the data model. Are there any conditional formatting - field value limitations when connecting to a tabular data model? The field value functionality seems to work through switch metrics for me when housing the data within the PowerBI file. Thank you!
