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

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

  • Bessonnizza1992's avatar
    Bessonnizza1992
    Helper I
    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 )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Bessonnizza1992 ，

      It's glad to hear that your problem has been resolved. And thanks for sharing your solution for us. Thank you.

      Best Regards

      Rena