COVID-19 reporting Employees and Key Workers Report
Hello Complete PowerBI and DAX newbie, but am tasked with identifying how many of our staff are available for work during the Coronavirus Pandemic - as many are key workers supporting hospitals. I was trying to avoid nested IF statements, but have been getting a bit bogged down in when to use Switch with measures and calculated columns. I downloaded DAX Studio - but then quickly realised that the scripts are not directly transferable between PowerBI and DAX Studio. I have created: Absence Status Can Work = SWITCH(TRUE(), 'COVID-19'[Covid-19 Status ]="SA-Confirmed Case",0, 'COVID-19'[Covid-19 Status ]="SA-Suspected Case", 0, 'COVID-19'[Covid-19 Status ]="SA-Self-Isolating No Symptoms", 0, 'COVID-19'[Covid-19 Status ]="STL-Dependency", 0, 'COVID-19'[Covid-19 Status ]="STL-Other WFH",0, 'COVID-19'[Covid-19 Status ]="STL-Self-Isolating WFH",0, 'COVID-19'[Covid-19 Status ]="STL-Unable to WFH Equipment",0, 'COVID-19'[Covid-19 Status ]="STL-Unable to WFH Role",0, 'COVID-19'[Covid-19 Status ]="STL-WFH Suspected Case",0, 'COVID-19'[Covid-19 Status ]="",1 ) and Absence Dates Expired = SWITCH(TRUE(), AND('COVID-19'[Covid-19 Status Start Date]<>BLANK(),'COVID-19'[Covid-19 Status End Date]=BLANK()), 0, 'COVID-19'[Covid-19 Status End Date]<TODAY(), 1, AND('COVID-19'[Covid-19 Status Start Date]=BLANK(), 'COVID-19'[Covid-19 Status End Date]=BLANK()),1, 'COVID-19'[Covid-19 Status End Date]>=TODAY(), 0 ) However, I cannot then discover how to make the connection to combine my 2 results to match the business logic below. I had wanted to avoid hard-coding text into my variables - as the 'business' is a moving target at the moment. At the end of the day I need to give a total number of 'available' employees, and then I can illustrate who we have as 'key workers' for times of pressure. I would be very grateful for any assistance, so that I can return to feeding my family and getting outside for an hour! many thanks. COVID-19 Status Business Logic Status Can Work Absence Period Start Date Absence Period End Date Absence Period Expired= TRUE Can work if Absence Dates has an End date <TODAY, or if (No dates entered for Start and End Dates) STL - Other WFH Available by default. 1 0=FALSE/1=TRUE STL - WFH Suspected Case Available by default. 1 0=FALSE/1=TRUE STL - Self-Isolating WFH Available by default. 1 0=FALSE/1=TRUE SA - Self-Isolating No Symptoms Not Available, but becomes Available once 'absence period' (End Date) expired 0 0=FALSE/1=TRUE SA - Suspected Case Not Available, but becomes Available once 'absence period' (End Date) expired 0 0=FALSE/1=TRUE SA - Confirmed Case Not Available, but becomes Available once 'absence period' (End Date) expired 0 0=FALSE/1=TRUE STL - Dependent Family Not Available by default. 0 0=FALSE/1=TRUE STL - Unable to WFH Equipment Not Available by default. 0 0=FALSE/1=TRUE STL - Unable to WFH Role Not Available by default. 0 0=FALSE/1=TRUE
Switch logic not pulling in column from another table
Hello, I am trying to adjust my Switch statemnt so that I do not have to tediously input vlaues when I have to update the dashboard. Above line "3" is my old logic. Line "5 " begins my new logic, where I am trying to say if the code = 1003, and the LOS O/E is < [P95] (which is coming from another table) then return "P95". Here is the [P95] column The error I get says "A single value for column "P95" cannot be determind. The relationbetween the tables are many to many (*:*) and being cross filtered in both directions.Switch Restricts to evaluate all the conditions
Using SWITCH the first condition met defines the result. In the following example, the second condition (<= 150) will never be met, because the first one is less restrictive. Is there any solution so that switch should go to each and every condition, Order of condition should not matter. DEFINE MEASURE Sales[Discounted Sales] = SUMX ( SUMMARIZE ( Sales, Sales[Net Price], Product[Category] ), VAR DiscountPct = SWITCH ( TRUE, Sales[Net Price] <= 1000, 0.2, Sales[Net Price] <= 150, 0.15, Product[Category] = "Audio", 0.13, 0 ) RETURN [Sales Amount] * (1 - DiscountPct ) ) EVALUATE SUMMARIZECOLUMNS ( 'Product'[Category], "Sales Amount", [Sales Amount], "Discounted sales", [Discounted Sales] ) ORDER BY [Category] #Power_BI #DAX_CommunityCombined SWITCH and SELECTEDVALUE function not working for sum of multiple measures
Hi all, I'm trying to set up a DAX-based table visual for a financial statement - based on financial transactions. My current set up is now based on a YouTube guide (https://www.youtube.com/watch?v=J4317R5BvsA&t=539s). The major difference between my approach and the one in the YouTube guide is that I'm working on financial transactions rather than aggrecated categories. So far so good, but I'm running into trouble when trying to get values from a measure that sums other measures e.g. a gross margin, which shows a blank. My measure for retrieving and lining up the correct line item values looks like this: The measures that do work, are all written like this: The measures that don't work, are all written like this: I'm not a super user of Power BI at all, but I suspect that the "sum measures" shows a blank due to the fact that my "sum measures" contain multiple measures. I tried replacing the DAX code with a simple hard typed numbers (e.g. 1000-500-250) formula, which does show up in my table. Any ideas for a workaround or a totally different approach is much appreciated. Happy holidays! 😎 Best regards, Chris
IF/SWITCH measure performance
Hi, I have a general question about the best way to write if/switch measures. Is any of the following examples (1 and 2) more efficient than the other? 1) if_measure = IF(condition_measure = value1, [measure1], [measure2]) switch_measure = SWITCH(condition_measure, value1, [measure1], value2, [measure2], [measure3]) 2) if_measure = IF(condition_measure = value1, DAX code for measure 1, DAX code for measure 2) switch_measure = SWITCH(condition_measure, value1, DAX code for measure 1, value2, DAX code for measure 2, DAX code for measure 3) Thanks!Writing Measure that Returns Customer Specific Revenue based on Customer Specific Multiplier
Hello, I am attempting to write a measure that examines the customer name before returning a customer specific revenue given a customer specific multiplier multiplied by the amount of items sold. For example, if the customer is customer 1 in this case, then we would take the customer 1 specific multiplier and multiply it by the number of items sold to get the customer 1 specific revenue (if customer = customer 1, then customer 1 multiplier * # of items sold = customer 1 revenue). However, the measure does need to be able to store multiple (up to 30) different customer specific multipliers and in turn know when to use each one based on filtering from a slicer that allows you to choose between customers. I have written the following measure that only appears to work for customer 1 and not the following customers in this example. The numbers 5, 10, and 15 are placeholder customer specific multipliers. Measure1 = SUMX (‘Table’, IF(‘Table’[Customer]= “Customer 1”, ‘Table’[Items Sold]*5, IF((‘Table’[Customer]= “Customer 2”, ‘Table’[Items Sold]*10, IF((‘Table’[Customer]= “Customer 3”, ‘Table’[Items Sold]*15 ….. Here is another measure I’ve attempted but have had no luck with: Measure2 = SWITCH( TRUE(), ‘Table’[Customer] = “Customer 1”, ‘Table’[Items Sold]*5, ‘Table’[Customer] = “Customer 2”, ‘Table’[Items Sold]*10, ‘Table’[Customer] = “Customer 3”, ‘Table’[Items Sold]*15, )) Any feedback and help are much appreciated.Currency Switch Measure is Mixing All Currencies at Row Level
Hello, We have a report built where a slicer can select between either US or CA. This is triggering a switch measure to select either the USD$ or CAD$ column from the DB Table: Sales $ = Switch(true(), [OnlyCanadaSelected]="No",[Sales (USD)], [OnlyCanadaSelected]="Yes",[Sales (Local)]) OnlyCanadaSelected = IF(COUNTROWS(FILTER(FlashSalesSites,FlashSalesSites[Country]<> "CA")) = 0,"Yes","No") When trouble shooting the model, I see that "Sales $" column shows local currency for both countries due to the row level context BUT the Grand Total for the matrix is totaling in USD$: I have tried multiple rewrites of the logic but cannot get around the row level. Also, I cannot understand why the Grand Total is correct? I would love some suggestions on how to correct! Thanks
