Good day, everyone.
I have problem with calculation of downtime for furnace. I'll explain to you with an example:
I have downtime that lasts 25 days (from March 3 till March 28). When i use slicer (that connected to dates) to set period, for example, from March 2 till March 29, this downtime counts correctly. But if i use period from March 4 till March 28, or March 3 till March 27 - my downtime disappears from the table, so i can't count correctly downtime for time period.
I tried to solve this problem by adding new tables/columns for existing table, BUT:
1) Power BI uses a lot of computer resources (like, really, A LOT), therefore computer stops to work properly;
2) Time of new table/column adding is increasing greatly (30-40 minutes for creating 1 column).
I tried to find the way for adding new rows by DAX formulas, but i didn't find the way when i use DirectQuery.
Do you have any suggestions for solving this problem?
Of course, the row(s) disappears since you're filtering the fact table on the exact days that you've chosen. I still don't quite get the logic you want to apply. Let's say that for one selected furnace the downtime was from date A to date B (A < B). If you select date A (first slicer) and date B (second slicer), the row, obviously, will be there. Do you want to see the row also when you select dates A' and B' where A<=A'<B'<=B? And you don't want this row to appear when you select A' and B' so that either A'< A or B < B'? Is this the logic you're after?
I need to make next presentation:
We have downtime report in our system, and i need to "duplicate" presentation of this report, but in Power BI.
I'll show you this presentation on screenshots:
We see on this screenshot one downtime, but as you can see if we change check period (red part), downtime will not disappear. It will remain, but it will be cut in end (check blue area).
@AlexandrVPat , What I see in the screenshot is logic that, the date shown on the left top is between the start and end dt(datetime) . Not the start and end dt is between dates on the right top. Can you share the formula you are using?
I should correct you left top - start date, right top - end date. Pale date - unactive date.
I don't use formulas for now, i connect to database directly for all information i need. For now i try to find the way to "cut" downtime if our report need dates, which intersect with downtime.
Unplanned downtime 12.01.19 00:05:00 12.01.19 00:35:00
Unplanned downtime 12.01.19 00:40:00 12.01.19 00:55:00
Unplanned downtime 12.01.19 01:00:00 12.01.19 01:16:00
Unplanned downtime 12.01.19 01:20:00 12.01.19 01:36:00
Unplanned downtime 12.01.19 01:40:00 12.01.19 01:58:00
Unplanned downtime 12.01.19 02:00:00 12.01.19 02:34:00
Unplanned downtime 12.01.19 02:40:00 12.01.19 02:56:00
Unplanned downtime 12.01.19 03:05:00 12.01.19 03:25:00
Unplanned downtime 12.01.19 03:30:00 12.01.19 04:00:00
Unplanned downtime 12.01.19 04:05:00 12.01.19 04:35:00
I apologize for not being able to show data in table format (some errors appears in message form)
As you can, i have type of downtime, start date and end date.
P.S. 1st column - type of downtime, 2nd column - start date, 3rd column - end date. I have other columns, but they are derivatives from 3 main columns.
As you can see in this screenshot, area circled in red marker - start date of downtime. Area circled in blue marker - end date of downtime. I need to count downtime circled in black marker.
But, if i use another dates (examples in screenshot) - downtime i need disappears.
We are excited to announce the Power BI Super Users!
Overview of Power BI 2020 release wave 2!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.