topic Re: Trying to convert old excel dashboard to Power BI but it uses CountIFS - Need Help Translating in DAX Commands and Tips

Trying to convert old excel dashboard to Power BI but it uses CountIFS - Need Help Translating

Anonymous — Mon, 19 Aug 2024 19:58:17 GMT

I am trying to convert this report from Excel into Power BI. Every morning I extract the data from our database and paste it into this report on two seperate sheets. "MTD Vol - RAW" and "Scheduled T+30 - RAW". Based on these two files the report pulls what the previous OR cases were based on their location into different buckets based on the days which are "hard numbers" because it's less than today. Today and future is based on scheduled cases for the OR areas in the same manner location and the day it is scheduled for. Which are in red because these numbers could change as the case could always be cancelled.

With moving the report to Power BI, I want to get three main things out of it.

Duplicating the Metrics on the top into "cards"
Reporting out the current dates estimated case volume. The 19th would get reported out as Main - 22, OSC - 21, CVOR - 1, GI - 7, Ridesdale - 10
Being able to append the data, so that on a seperate tab I can display a YoY by month Line chart with a target of the current FY Budget numbers against actual volume based on location and then I could have a drill down by surgeon etc.

All data has been scrubbed and made fictional based on major league baseball team line-ups. Along with locations and any other sensitive data.

=IFERROR(
IF($B22<TODAY(),
(COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B22,'MTD Vol - RAW'!$A$2:$A$2622,"SJS OSC OR")),
(COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B22,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX OSC OR"))
),0)-M22

Another Code to get the information is:

=IFERROR(
IF($B15<TODAY(),
SUM(COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B15,'MTD Vol - RAW'!$A$2:$A$2622,"TEX Virtual Procedure",'MTD Vol - RAW'!$L$2:$L$2622,$AK$3),COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B15,'MTD Vol - RAW'!$A$2:$A$2622,"TEX Main OR",'MTD Vol - RAW'!$L$2:$L$2622,$AK$3)),
SUM(COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B15,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX Virtual Procedure",'Scheduled T+30 - RAW'!$O$2:$O$3500,$AK$3),COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B15,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX Main OR",'Scheduled T+30 - RAW'!$O$2:$O$3500,$AK$3))
),0)-G15

Maybe they just over complicated the worksheet, I inherited this when I took the position.

https://drive.google.com/file/d/11b1A4MZiXoAP89gVBpQBPdcH-XIsyhNc/view?usp=drive_link

Re: Trying to convert old excel dashboard to Power BI but it uses CountIFS - Need Help Translating

Greg_Deckler — Mon, 19 Aug 2024 20:38:04 GMT

Anonymous That's generally:

COUNTX(FILTER(...)...) or CALCULATE(COUNT(),FILTER(...))

CO-CU Excel to DAX Translation - Microsoft Fabric Community

Re: Trying to convert old excel dashboard to Power BI but it uses CountIFS - Need Help Translating

Anonymous — Tue, 20 Aug 2024 14:12:33 GMT

I've tried or I should say horribly failed at trying to write something using that. As I thought maybe I was over complicating it and adding to much to the script. As I only want the case to be counted and included in the sum if it matched the date to the left. Which is always going to be a singular day in the current month. If it is current date or future date then it has to pull the sum from the scheduled sheet data. I guess that's where I am not formatting it properly.

Count of Patient Class running total in Day =

CALCULATE(

COUNTA('MTD Vol - RAW'[Patient Class]),

FILTER(

ALLSELECTED('MTD Vol - RAW'[Date].[Day]),

ISONORAFTER('MTD Vol - RAW'[Date].[Day], MAX('MTD Vol - RAW'[Date].[Day]), DESC)

But I need to seperate "Inpatient" cases which is everything except Short Stay/Outpatient Surgery. Which is what the excel sheet does, by referencing the cell AK3 which contains that text.

Inpatient =

=IFERROR(
IF($B8<TODAY(),
SUM(COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B8,'MTD Vol - RAW'!$A$2:$A$2622,"TEX Main OR",'MTD Vol - RAW'!$C$2:$C$2622,"*Orthopedics*"),COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B8,'MTD Vol - RAW'!$A$2:$A$2622,"TEX Main OR",'MTD Vol - RAW'!$C$2:$C$2622,"*Podiatry*")),
SUM(COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B8,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX Main OR",'Scheduled T+30 - RAW'!$E$2:$E$3500,"*Orthopedics*"),COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B8,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX Main OR",'Scheduled T+30 - RAW'!$E$2:$E$3500,"*Podiatry*"))
),0)-G8

Outpatient =

=IFERROR(
IF($B8<TODAY(),
SUM(COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B8,'MTD Vol - RAW'!$A$2:$A$2622,"TEX Main OR",'MTD Vol - RAW'!$C$2:$C$2622,"*Orthopedics*",'MTD Vol - RAW'!$L$2:$L$2622,$AK$3),COUNTIFS('MTD Vol - RAW'!$B$2:$B$2622,$B8,'MTD Vol - RAW'!$A$2:$A$2622,"TEX Main OR",'MTD Vol - RAW'!$C$2:$C$2622,"*Podiatry*",'MTD Vol - RAW'!$L$2:$L$2622,$AK$3)),
SUM(COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B8,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX Main OR",'Scheduled T+30 - RAW'!$E$2:$E$3500,"*Orthopedics*",'Scheduled T+30 - RAW'!$O$2:$O$3500,$AK$3),COUNTIFS('Scheduled T+30 - RAW'!$B$2:$B$3500,$B8,'Scheduled T+30 - RAW'!$A$2:$A$3500,"TEX Main OR",'Scheduled T+30 - RAW'!$E$2:$E$3500,"*Podiatry*",'Scheduled T+30 - RAW'!$O$2:$O$3500,$AK$3))
),0)

Am I going in the wrong direction for this build (remodel)?