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 |
