Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
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
@LT_Maverick That's generally:
COUNTX(FILTER(...)...) or CALCULATE(COUNT(),FILTER(...))
CO-CU Excel to DAX Translation - Microsoft Fabric Community
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
14 | |
11 | |
10 | |
7 |
User | Count |
---|---|
25 | |
15 | |
15 | |
14 | |
13 |