Concatenate 3 column values as icons
I am tracking Sales to Pace (target) and need to display a matrix column with past 3 month's results as 3 concatenated icons. Example with : Here's my base DAX I've trying different methods with: $ to Pace Cumulative = 'DAX Measures'[Created] - 'DAX Measures'[Combined Pace Cumulative Table] $ to Pace LCM = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -1) $ to Pace LCM-2 = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -2) $ to Pace LCM-3 = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -3) $ to Pace LCM Icon = SWITCH(TRUE(), 'DAX Measures'[$ to Pace LCM] > 0, UNICHAR(9650), 'DAX Measures'[$ to Pace LCM] < 0, UNICHAR(9660), BLANK()) $ to Pace LCM-2 Icon = IF('DAX Measures'[$ to Pace LCM-2] > 0, "✅", "❌") $ to Pace LCM-3 Icon = IF('DAX Measures'[$ to Pace LCM-3] > 0, "🟢", "⭕") Combined Icons = [$ to Pace LCM-3 Icon] & " " & [$ to Pace LCM-2 Icon] & " " & [$ to Pace LCM Icon] The [Combined Icons] column is [Last 3 Months] in example matrix. Since I'm trying to represent the value of [$ to Pace Cumulative] as an icon only, I haven't been able to come up with a measure for conditional formatting that correctly designates a color for the unicode triangles or an icon for the values of concatenated measures. Ideally, I want to display these native PBI icons for above 0 and below 0: However, the following DAX doesn't work: $ to Pace LCM-2 Icon = IF('DAX Measures'[$ to Pace LCM-2] > 0, "TriangleHigh", "TriangleLow") I also couldn't get image URLs to work in this measure. Any idea how I can display the [Last 3 Months] column using the above native Power BI green and red triangles?
When transforming a decimal measure into text the dot decimal seperator turns into comma
I have a measure that returns a decimal format "0.0" For a card I require to report it side by side with another measure using a text format = [Decimal measure A ] && "/" [Decomal measure B] It results both decimal measures seperators to turn from a dot to a comma. Using the format function also did not work: = Format( [Decimal measure A ], "0.0") && "/" Format([Decimal measure B] "0.0") Regional setting are fixed for US English. Your help appreciated. Thank you, LiamWhy do my SWITCH measure results have a long number despite selecting 2 decimals?
To give a bit more info, this is the main part of how I insert the values into the SWITCH measure. Please note it's a very shortened version, but includes the two main types of values (with and without additional text) var delta_etc_pfe_perc = IF( LEN([Δ ETC vs PFE %]) > 0, [Δ ETC vs PFE %], 0 ) var result = SWITCH( TRUE(), /* Metric = Days */ metrics = 1 && selected_column_1 = "Planned", [Frc Days], metrics = 1 && selected_column_1 = "Δ ETC vs PFE", [Δ ETC vs PFE] & " (" & delta_etc_pfe_perc & ")", So in this case, my value Delta ETC vs PFE is numerical, but the variable I insert after is text because SWITCH doesn't allow me to insert numerical percentage variables, as they will become reformatted as non percentage. So I format as text the measure as I calculate it on its own as follows: FORMAT(Result, "0.00%") and then here make that if() call on it As you can see I also added a measure to conditionally colour only some of the values, and I just want to state beforehand that changing that won't affect the result. Why do my SWITCH measure results look like this? I already fixed the decimal number to 2 values. It only happens for those rows where I stated that it should give me both the result of an operation and then another one within parenthesis. But both of those values I also fixed to 2 decimals only
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!
Using Format function with Calculated Groups to convert numbers to millions doesn't work properly
Hello. I have some data with big numbers and what i want is to create a slicer so a user can choose what format he want to see: usual (like 1 234 567) or in millions (1.2 M). To make this i created a calculation group with two values - first one is nothing but a SELECTEDMEASURE() and the second one is FORMAT function (you can see it on screen). The problem that when i activate Mln option on slicer it works only with card or table visual but don't work with line or bar chart. I guess the problem may be because it convers it to text format and it can't calculate it properly or something else. Anyway, any suggestions to force it work on every visual? Without applying format: With format:
