cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021