- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Trying to convert old excel dashboard to Power BI but it uses CountIFS - Need Help Translating
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@LT_Maverick That's generally:
COUNTX(FILTER(...)...) or CALCULATE(COUNT(),FILTER(...))
CO-CU Excel to DAX Translation - Microsoft Fabric Community
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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
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)

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
10-12-2023 02:06 PM | |||
07-12-2021 03:28 PM | |||
10-14-2022 05:55 AM | |||
Anonymous
| 11-01-2023 08:08 AM | ||
04-11-2024 11:02 AM |
User | Count |
---|---|
24 | |
15 | |
9 | |
9 | |
8 |
User | Count |
---|---|
19 | |
15 | |
14 | |
13 | |
13 |