Forum Discussion

NightStalker's avatar
NightStalker
Frequent Visitor
5 years ago
Solved

Displaying Max value of every 8 hours

I'm using custom Streaming Data. I use an ESP32 to push the real-time weight value of a sensor to Power BI API. However, I need a table displaying the weight max value of every 8 hours. My date colum...
Need Help
  • NightStalker's avatar
    NightStalker
    5 years ago

    I managed to do all of it. for the Peak, I used this Measure 

    ShiftPeak = CALCULATE(MAX(RealTimeData[Weight]), TOPN(1, RealTimeData, RealTimeData[DateShift], ASC, RealTimeData[Shift], ASC))  . However, I did a bit of manipulation from the ESP, so I push a DateShift (which is basically just a number value) and three shifts that already got determined by the ESP. Everything will be displayed fine as long as you don't choose Date (The Value that has DateTime. Note this is for API streaming dataset). The other problem that I was struggling with is to calculate the sum of the shift peaks, which miraculously worked with this measure 
    Shiftsum = SUMX(SUMMARIZE(RealTimeData, RealTimeData[DateShift], RealTimeData[Shift], "Peaks", MAX(RealTimeData[Weight])),[ShiftPeak])  .
    If you want to associate every ShiftPeak to which time it's occurred, I used another measure which is 
    PeakDate = CALCULATE(MAX(RealTimeData[Date]), TOPN(1, RealTimeData, RealTimeData[DateShift], ASC, RealTimeData[Shift], ASC, RealTimeData[Weight])) & " "  . (if you remove " ") the time won't be displayed for some reason, but the date will. 
    I'm sorry for the late reply cause I managed to do it way back. I'm just on a tight schedule to make a report about this project that I was working on. I hope this is very much helpful. 

     

NightStalker's avatar
NightStalker
Frequent Visitor
5 years ago

 

 

Hey thanks for the reply! I managed to push a fixed date and the shift from the ESP; However, the Earlier DAX don't work in real Time Data :S. It would've made everything easy. any suggestions.

NightStalker's avatar
NightStalker
Frequent Visitor
5 years ago

 

I managed to do it; however, I can't assign the date to it, as it shows all the values. I pushed DateShift which is a unique value for every day as it contains the 3 shifts during one day, and every shift is an 8-hour interval. Funny enough, I have no idea how I can calculate the sum of the ShiftPeak.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi NightStalker,

    >>Funny enough, I have no idea how I can calculate the sum of the ShiftPeak.

    You can create a table visual with raw category fields and shiftpeak as category(click on them to choose 'do not summarize'), then you can add date field to this visual and choose aggerated mode 'max'/'last'.

    In addition, can you please provide some dummy data to test? (keep raw table scheal and remove sensitive data fields)
    Regards,
    Xiaoxin Sheng

    • NightStalker's avatar
      NightStalker
      Frequent Visitor
      5 years ago

      Here you go. I can't share PBIX, as it's connected to real-time Data... CSV FILE 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi NightStalker,

        You can take a look at the following steps if helps:

        1. Create a calculated field based on 'hourshift' to group these records.

        Shift Group = 
VAR offset =
    INT ( HOUR ( [HourShift] ) / 8 )
RETURN
    offset
        + IF ( DATEDIFF ( TIME ( 8 * offset, 0, 0 ), [HourShift], SECOND ) > 0, 1, 0 )

        2. Create a table visual with category fields and choose the summary mode of value fields to max.

        Notice: other fields are setting to 'do not summarize'.

        Regards,

        Xiaoxin Sheng