Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
We have an incidents database that records each time a vehicle is "EARLY", "LATE', and "NORMAL or On Time" to a certain bus stop and we need to determine the percentage of time vehicles are early, late or normal based on the transit day. The problem is the transit day falls between 3 a.m. (today) and 4 a.m. (tomorrow) and I don't know how to filter for that. I created a calendar table then created two fields that capture the Start of the Transit Day and the End of the Transit day but have no idea how to use it, yes, I am very new to this, my apologies.
I have a filter that I found on this forum that works great to capture the incidents within the normal 24 hour day starting at midnight and ending at midnight but I need to capture incidents between today @ 3 am and tomorrow at 4 a.m. as the "transit day" and have no idea how to incorporate the calendar table with this measure (I'm hoping it's as easy as plugging it in but please let me know what I could do to get to the end result):
Sum if Early = CALCULATE(COUNT([incident_name]),FILTER(OTP_Report_SQL,SEARCH("Early",[incident_name],1,0))))
I have the same filter for "LATE" and "NORMAL"
Here are the two tables I am working off. Any help or direction would be greatly appreciated!
Thank you in advance, so much for your guidance!
Solved! Go to Solution.
Hi @MRGMSR,
There can be 2 totals
Please download the updated powerpivot file
Hi ChandeepChhabra,
That did the trick!!! Thank you so much. I truly appreciate your working on this for me.
Thanks,
MRGMSR
Hi @MRGMSR
First I added a date column (to strip off time) in your "OTP_Report_SQL" table by using the formula =INT(OTP_Report_SQL[Incident_date_time])
I am hoping that you already have the relationship set up between "OTP_Report_SQL" table and Transit_Day_Time Table
Please modify your measure
SumifEarly=
CALCULATE(COUNT([incident_name]),OTP_Report_SQL[incident_name]="Early",FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))
Please check if it gives you the desired result. You can download a mock powerpivot file that I created
Hi ChandeepChhabra,
Thank you SO MUCH for this! It worked but as it usually goes, now I have another problem. Now I need to get the percentage of the total number of incidents per transit day so I used your calculation to calculate the count of incidents (Early, Late, Normal) and tweaked it to calculate the (total) count of incidents per transit day (below) but when I try to create a measure to provide the percentage of the "Early", "Late", "Normal" incidents per transit day using the SumifEarly divided by the TotalIncidents2 measure:
(Sumifearly2 = CALCULATE(COUNT([incident_name]),OTP_Report_SQL[incident_name]="Early",FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))
/
TotalIncidents2 = CALCULATE(COUNT([incident_name]),OTP_Report_SQL[incident_name],FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))
I get the error below:
MdxScript(Model) (18,75) Calculation error in measure 'OTP_REport_SQL'[TotalIncidents2]: Cannot convert value 'NORMAL' of type Text to type True/False.
Any ideas on what I should do? I am looking up this message online and on this forum to see if I can get a solution but if you know off the top of your head, I'd greatly appreciate your help once again!
Thank you
MRGMSR
Hi @MRGMSR,
There can be 2 totals
Please download the updated powerpivot file
Hi ChandeepChhabra,
That did the trick!!! Thank you so much. I truly appreciate your working on this for me.
Thanks,
MRGMSR
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
91 | |
89 | |
77 | |
69 | |
69 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |