Time Intelegence

[Bessonnizza1992]

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:

 What i need:

 What measure i need to write to get red grapfic? Table of time already have.

[amitchandak]

@Bessonnizza1992 , Under shape, Switch on Stepped.

[Bessonnizza1992]

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]

Hi @Bessonnizza1992 ,

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

Best Regards

Rena

[Bessonnizza1992]

@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]

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 · Accepted solution]

@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]

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