cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Getting the latest record of given timeframe

Hi All! I have a heat map that measures the uptime of equipment for the current hour and each of the 8 hours prior:

The problem lies in the fact that a given hour may not always have records; in the above example, the machine was running from 4 AM to 6 AM non-stop and did not have any records for the 5 AM hour to indicate that it had been up and running that entire hour. The -3 hour should be showing 100% but it is incorrectly showing 0%.

This visualization has a separate measure for each hour, calculating the uptime for each hour. For example, Hour -3 has a calculation as follows:

``````-3 = IF(ISBLANK(CALCULATE(COUNTROWS(UptimeCombined), FILTER(UptimeCombined, UptimeCombined[Hours_Offset] = 3))) = TRUE,
(CALCULATE('UptimeCombined'[Uptime %], FILTER(UptimeCombined, UptimeCombined[Hours_Offset] > 3 && UptimeCombined[ShiftDateTime] = MAX(UptimeCombined[ShiftDateTime])))),
(CALCULATE('UptimeCombined'[Uptime %], FILTER('UptimeCombined', 'UptimeCombined'[Hours_Offset] = 3))))``````

If the row count comes back BLANK, then I need look BACK in time (max record where Hour_Offset > 3) and find the status (either "RN" or "DN") of whatever the last record was and display that uptime value for that hour (100% or 0%, respectively) but I can't get that part of the measure to work properly; the -3 hour should show 100% because the latest record before then had status of "RN" (in the -4 hour). Here is what the data looks like:

What is the correct DAX I need in Line 2 of this measure to set the uptime to either 100% or 0%, based on the latest record from before that hour? Thanks in advance! I love this community and all the help that you folks provide.

1 ACCEPTED SOLUTION
Solution Sage

Roughly speaking:

1. Generate a table with 1 column where you'll store all the hours you need.

2. Join this table to the data you have via LEFT OUTER JOIN.

3. Sort the data accordingly.

4. Use the Fill Down command to fill in the gaps where appropriate.

Can't tell you more since I don't have your data and can't see the model/measures.

This could help you Quickstart - using Power Query in Power BI | Microsoft Docs but I'd also try the Community Forum for Power Query.

4 REPLIES 4
Solution Sage

Roughly speaking:

1. Generate a table with 1 column where you'll store all the hours you need.

2. Join this table to the data you have via LEFT OUTER JOIN.

3. Sort the data accordingly.

4. Use the Fill Down command to fill in the gaps where appropriate.

Can't tell you more since I don't have your data and can't see the model/measures.

This could help you Quickstart - using Power Query in Power BI | Microsoft Docs but I'd also try the Community Forum for Power Query.

Frequent Visitor

Just to follow up, I ended up going back to the source view in SQL Server and filled in the gaps there before it ever even hits Power BI. Your sagely advice about changing the model first was the key here and it set me on the right path. Thank you!

Solution Sage

It's the model that's at fault here. Please change the model to make simple DAX calculations instead of trying to write complex measures that in a month's time nobody will be able to follow, including you.

If you have gaps in your data, then please use Power Query to get rid of them---that's what PQ is for: to make data consistent and easy to work with. Once you have a continuous set of data for each hour for each machine/equipment, the calculations will be very easy. On top of that, if I were you, I would get rid of the measures you have for each offset but create a dimension with offsets (Current Hour, -1, -2,...) and then create one measure that would adjust to the currently visible offset. Such a design is in harmony with the PBI philosophy and will not only be easier to understand and manage but also more flexible (not to mention proliferation of measures will be avoided).

Frequent Visitor

Thanks a bunch for your reply. That makes a lot of sense. Sounds like I should really have at least one row for every hour, at a bare minimum, to simplify things and avoid this issue. That said, my experience in M is zero... To fill these gaps, what would you tell Power Query in order to generate a row for a given Hour_Offset if none exist for the given equipment? I am more than happy to learn, I just don't know where to start.

Announcements