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