Help with complex DAX measure

[simonmcd]

Hi everyone, 

I'm pretty new to Power Bi/DAX and jumping into the deep end. I've got a data set that comes from a device monitoring system and trying to work out the up time (total time the device is online) from a metric history alert table. This is the table which I have simplified in a visual as there's more data and too much to do a screen shot, it's filtered to show a single device. Unfortunately the data isn't reported as a heart beat and an event is only logged when it changes state where it gives the current state, previous state and duration from previous event to current event (in seconds). I then have an asset table which contains the name, id, location, etc and here is where I can using a measure to calculate the up time for each device. I believe I need to use measures as with calculated columns I'm not able to get the date slicer values. 

On my report I have a number of slicers where I can select a room, a device and finally a date slicer (as a slider) where I can select the begining and end date I want to get the uptime for each device. I have a couple of issues I need to resolve.

Issue 1 - I have a DAX forumula that calculates the sum of the duration values for "NoNetwork" entries in the table just as an initial calculation (I know there is more to do after this) but I'm struggling to get the correct DAX to compare MetricHistory[TargetId] and Asset[_id] in the filter for the CALCULATE. This DAX works but only if I use the slicer to filter down to a single device. I understand why this is happening but can't find a way to resolve. I think part of my problem is I need to use some features of a measure and some of a calculated column but not sure how to do this.

UpTimeMeasureV3 = 
var startDate = MIN('Calendar'[Date])
var endDate = MAX('Calendar'[Date])
var startDateFromCalendar = CALCULATE(MIN('Calendar'[Date]), ALL('Calendar'))
var currentAsset = VALUES(Asset[_id])

var duration = CALCULATE(sum(MetricHistory[Duration]), MetricHistory, MetricHistory[TargetId] = currentAsset, MetricHistory[Value] = "NoNetwork")

return startDate  & " | " & endDate & " | " & startDateFromCalendar & " | " & currentAsset & " | " & duration

Issue 2 - When I select a date from the slicer I need to find out what the state of the connection is (online or offline) if there is no data then I will need to find out what the previous state is by looking outside of this date range. Without any data to work with I'm struggling to work out how I can get this data from the tables.

I think I may have bitten off more than I can chew! I am going to work on an example power bi project (I'm unable to share current project) but wanted to post this up first to see if anyone had any suggestions ahead of me completing this.

Thanks in advance

[lbendlin · Accepted solution]

deep end indeed. 

- your data model needs fine tuning. Explain why you chose the bidirectional search between asset and metrichistory

- depending on which side you choose the date column from you will get different behavior. If you choose it from the metrichistory then you will only see dates where something happened. If you choose it from the dates table then you can pick full ranges regardless if anything happened or not.

Your data source is the best thing here - event based reporting is a little tricky but it gives you unlimited flexibility unlike snapshot based reporting. You will need to put in some work to compute device status at a certain time (use a measure for that). If you want I can describe the basic steps for how to do that.

[simonmcd]

thanks for the reply, greatly appreciated given the complexity of the task.

I had bidirectional search as this is only a part of the data I need to process and have broken it down into sections to make it more manageable. The bidirectional search was needed when adding another element so decided I wanted to keep this to make it similar to the finished product. 

I have my slicer to choose the dates table to give me the full range of possible dates regardless of the event data.

If you could give me the basic steps that would be really helpful

thanks