Forum Discussion

Bessonnizza1992's avatar
Bessonnizza1992
Helper I
5 years ago
Solved

Time Intelegence

I have an array of data where Value can only be in two states 1 or 0. Each value has its own timestamp of the event change from 1 to 0, etc.   When i build line chart PowerBI return me this: ...
Need Help
  • Bessonnizza1992's avatar
    Bessonnizza1992
    5 years ago

    Anonymous PBI freezes too with this measure:(

     

    I tried to use a different construction, now everything is fine. Ty for help!

     

    Measure =
VAR currDate =
    MAX ( CalendarTime[Time] )
VAR prev =
    CALCULATE (
        MAX ( Statistics[Timestamp] ),
        FILTER ( ALLSELECTED ( Statistics ), [Timestamp] <= currDate )
    )
RETURN
    CALCULATE (
        MAX ( Statistics[Value] ),
        FILTER ( ALLSELECTED ( Statistics ), [Timestamp] = prev )
    )

     

Bessonnizza1992's avatar
Bessonnizza1992
Helper I
5 years ago

Ty for reply, but this option don't help me.

 

For example i have two points 

Value 1 - Timestamp 00:00:00

Value 0 - Timestamp 00:21:37

 

So mesuare must calculate all points beetween this. 

Value 1 - Timestamp 00:00:01

Value 1 - Timestamp 00:00:02

Value 1 - Timestamp 00:00:03

Value 1 - Timestamp 00:00:04

and so on.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Bessonnizza1992 ,

    What's your desired result? According to your example data, whether the table is what you want:

    Best Regards

    Rena

    • Bessonnizza1992's avatar
      Bessonnizza1992
      Helper I
      5 years ago

      Anonymous , thank you for reply!

       

      Yes, this table is exactly what i need. I managed to write a measure that fill down values, but CalendarTime table have 86400 row, and when i try to add measure in values (line chart) PBI is freze.

       

      As a result i want to buid this chart:

       

      I have an idea with another measure. I don't need to fill in all the lines, need only get the value of the previous non blank line and substitute it into the (current line index - 1). Mb you know how can i do it?

      Here my example https://dropmefiles.com/vH1NG

       

      Best regards!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Bessonnizza1992 ，

        I updated your sample pbix file, please check whether that is what you want. I create a measure  as below to get the values:

        Measure = 
VAR _curtime =
    MAX ( 'CalendarTime'[Time] )
VAR _curvalue =
    CALCULATE (
        MAX ( 'Statistics'[Value] ),
        FILTER ( 'Statistics', 'Statistics'[Timestamp] = _curtime )
    )
VAR _pretime =
    CALCULATE (
        MAX ( 'Statistics'[Timestamp] ),
        FILTER (
            ALL ( 'Statistics' ),
            'Statistics'[Timestamp] < _curtime
        )
    )
VAR _prevalue =
    CALCULATE (
        MAX ( 'Statistics'[Value] ),
        FILTER ( ALL ( 'Statistics' ), 'Statistics'[Timestamp] = _pretime )
    )
RETURN
    IF ( ISBLANK ( _curvalue ), _prevalue, _curvalue )

        Best Regards

        Rena