Sum of Failures Last Hour

[Shawn_Eary]

I'm still fighting with DAX.  I have a Streaming Push DataSet so I don't think I'm able to use Power Query M or DAX for Calculated Columns.  Instead, I apparently have to use a DAX Measure and the filters are kicking my tail.

I have a Streaming Push Data Set that looks a lot like this:

SpaceStation Table
DateTime	UnitId	Status	FailuresLastHour_Measure
4/20/2020 12:00	OXYBACKUP1	Down	1
4/20/2020 12:00	OXYBACKUP2	Up	1
4/20/2020 12:00	GRAVITY1	Up	2
4/20/2020 11:45	OXYBACKUP1	Up	0
4/20/2020 11:45	OXYBACKUP2	Up	1
4/20/2020 11:45	GRAVITY1	Up	3
4/20/2020 11:30	OXYBACKUP1	Up	0
4/20/2020 11:30	OXYBACKUP2	Up	2
4/20/2020 11:30	GRAVITY1	Down	4
4/20/2020 11:15	OXYBACKUP1	Up	0
4/20/2020 11:15	OXYBACKUP2	Down	2
4/20/2020 11:15	GRAVITY1	Down	4
4/20/2020 11:00	OXYBACKUP1	Up	0
4/20/2020 11:00	OXYBACKUP2	Up	1
4/20/2020 11:00	GRAVITY1	Down	4
4/20/2020 10:45	OXYBACKUP1	Up	0
4/20/2020 10:45	OXYBACKUP2	Down	1
4/20/2020 10:45	GRAVITY1	Down	3
4/20/2020 10:30	OXYBACKUP1	Up	0
4/20/2020 10:30	OXYBACKUP2	Up	0
4/20/2020 10:30	GRAVITY1	Down	2
4/20/2020 10:15	OXYBACKUP1	Up	0
4/20/2020 10:15	OXYBACKUP2	Up	0
4/20/2020 10:15	GRAVITY1	Down	1
4/20/2020 10:00	OXYBACKUP1	Unknown	0
4/20/2020 10:00	OXYBACKUP2	Unknown	0
4/20/2020 10:00	GRAVITY1	Unknown	0

Unfortunately, I can't seem to figure out what code to use to calculate the DAX Formula for the FailuresLastHour_Measure column.

A related quesiton I asked is here:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Sum-After-Grouping/m-p/1028186
Unfortunately, DAX confuses me so much I can't figure out how to extend the answer from @camargos88 to fit my above situation.

I know.  This is really embarrasing with me having to keep coming back here for so much help, but I'm really not getting it.  It seems like this would be easy to do in SQL Server or C#, but the DAX Filters and correct use of CALCULATE and GROUP BY seems to be tearing me to pieces.

[Greg_Deckler]

So perhaps something like:

Measure =
  VAR __Current = MAX('Table'[DateTime])
  VAR __Past1Hour = __Current - 1/24
  VAR __Table = 
    FILTER(
      'Table',
      'Table'[UnitId] = EARLIER('Table'[UnitId]) &&
        'Table'[Status] = "Down" &&
          [DateTime] >= __Past1Hour &&
            [DateTime] <= __Current
    )
RETURN
  COUNTROWS(__Table)

[Shawn_Eary]

Not sure if I'm entering the DAX Formula correctly, but when I use that code from @Greg_Deckler , I think I may be getting the error EARLIER/EARLIEST refers to an earlier row context which doesn't exist on the 
EARLIER('Table'[UnitId])
clause.  See Picture:

No Exist Error

I came up with the MS SQL Code I need this morning (see above), but I still haven't figured out what's going wrong with the DAX.  DAX is the prefered solution becuase I don't want to scrap my Push Streaming Dataset in favor of an MS SQL Server backend with a DirectQuery connection right now.

[Greg_Deckler]

Shoot, I messed up my measure and column syntax. Try this:

Measure =
  VAR __Current = MAX('Table'[DateTime])
  VAR __UnitId = MAX('Table'[UnitId])
  VAR __Past1Hour = __Current - 1/24
  VAR __Table = 
    FILTER(
      'Table',
      'Table'[UnitId] = __UnitId &&
        'Table'[Status] = "Down" &&
          [DateTime] >= __Past1Hour &&
            [DateTime] <= __Current
    )
RETURN
  COUNTROWS(__Table)

[Greg_Deckler]

If your space station crashes, I never posted anything...

[camargos88]

Hi @Shawn_Eary ,

I didn't get what you want.

Can you provide more details, please ?

Ricardo

[Shawn_Eary]

Given the data in the DateTime, UnitId and Status columns of the SpaceStation Table, I'm trying to determine the code for a measure that computes the number of times each system (or unit) failed within the LastHour. By hour, I mean within the last 59 minutes...

At 10:00, there are no known "Down" Status values so we can "assume" there have been no failures within the last hour. At 11:45, however, we can see that the GRAVITY1 system also failed at 11:00, 11:15 and 11:30 but *not* at 11:45 so we can assume there have only been three failures for GRAVITY1 within the last "hour". I do not count the failure of gravity1 at 10:45 since that occurened more than 59 minutes ago. This isn't a big deal at all and I can proably solve this with SQL (or C#) [1] but it bugs me that I can't figure out how to do it in DAX. I'm sorry to bother you, but I think I'm going to wait until tomorrow before I look at this again. I mentioned you because you solved a very important part of the problem for me in your solution to the "Sum After Grouping" question. There are two parts to this problem. The ability to correctly group results by UnitId *and* the ability to filter each of those groups by only what happened in the last 59 minutes. I've seen some posts on the interenet on how to fiter via DAX for the last several days but they are quite messy. I hope I haven't burdened you too much. I'm going to look at this again tomorrow. I've already been fussing with this for days...

UPDATE:
Assuming my eyes aren't playing tricks on me, the MS SQL Server code for the fourth column is shown here:

select *,
  (select count(*) from SpaceStation SS_Inner
  where 
  (Status = 'Down') and
  (SS_Inner.UnitId = SS_Outer.UnitId) and
  (SS_Inner.DateTime > (DATEADD(hour, -1, SS_Outer.DateTime))) AND 
  (SS_Inner.DateTime <= SS_Outer.DateTime)
  )
from SpaceStation SS_Outer order by DateTime desc, UnitId desc;

However, I would rather use DAX.  The problem is I just don't understand DAX.  Thankfully, the kind help of people like @Greg_Deckler and @camargos88 is making this easier.

[1] - To solve the problem in MS SQL Server, I would have to ditch the Streaming Push DataSet.  To solve in C# I would have to maintain state on the server.  Presently, my server that is pushing data to the Streaming Push Dataset is "stateless".

[camargos88 · Accepted solution]

Hi @Shawn_Eary ,

Try this measure:

Measure =

VAR _unitID = SELECTEDVALUE('Table'[UnitId])

VAR _date = SELECTEDVALUE('Table'[DateTime])

RETURN CALCULATE(COUNTROWS('Table'); FILTER(ALL('Table'); 'Table'[UnitId] = _unitID && 'Table'[Status] = "Down" && 'Table'[DateTime] >= (_date - (1/24)) && 'Table'[DateTime] <= _date)) + 0

Ricardo

[Shawn_Eary]

The answer by @camargos88 was the most complete.  I changed the semicolons to commas and changed

@camargos88 's 
'Table'[DateTime] >= (_date - (1/24))
to 
'Table'[DateTime] > (_date - (1/24))

Then I got the output I expected.  These are the results I have as of right now with @camargos88 's solution as the answer:

Results

The pretty version of @camargos88 's code is here:

camargos88 (Failures Last Hour) = 
   -- https://community.powerbi.com/t5/DAX-Commands-and-Tips/Sum-of-Failures-Last-Hour/m-p/1039560/highlight/true#M13897
   VAR _unitID = SELECTEDVALUE('Table'[UnitId])
   VAR _date = SELECTEDVALUE('Table'[DateTime])
RETURN 
   CALCULATE(
       COUNTROWS('Table'), 
       FILTER(
           ALL('Table'), 
           'Table'[UnitId] = _unitID && 
           'Table'[Status] = "Down" && 
           'Table'[DateTime] > (_date - (1/24)) && 
           'Table'[DateTime] <= _date
        )
    ) + 0

NOTE: I do not know if is possible to accept more than one solution so @camargos88  will be marked as the solution.

[camargos88]

Hi @Shawn_Eary ,

Good job, you've changed the code...it means you understood what it's executing.

You can accept more than one solution, please do it.

It's good because we can solve the question using different approaches.

Ricardo

[Shawn_Eary]

---

You can accept more than one solution, please do it.

---

Well, I'm likely going to deploy your solution since it was the first complete one that I received, but I'd like to know what's going on with the solution from @Greg_Deckler .   While this question is about a real word problem I'm facing, it's more about me trying to understand DAX.  Both you ( @camargos88 ) and @Greg_Deckler  have already helped me with that and I appreciate it.

BTW: I know this isn't the first time I'm complained about this, but I really wish the Web Masters would turn on automatic spell check for this Text Area instead of forcing me to click on the "ABC Button".  It's an easy change...
https://www.geeksforgeeks.org/html-spell-check/