cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

pull values ​​down

Hello all!

 

How to pull values ​​down, need measure, not PQ.

 

Bessonnizza1992_0-1601733227545.png

 

10 REPLIES 10
Resolver IV
Resolver IV

@Bessonnizza1992 
I have seen your file..The Measure you have written will not return any value neither on 00:04:59 timestamp or on any other timestamp which has blank on previous timestamp.look at the 00:00:01, your previous time stamp 00:00:00 has 0 min value so therefor you will get the min value of 00:00:00 into the 00:00:01 time stamp.but you will get the blank value of 00:00:02,00:00:03 and so on.beacause,there are no min of value on previous time stamp like (00:00:01 or 00:00:02).

The measure i have provided  will not allow you to execute beacuse of heavy row size in CalendarTime table.The idle section of doing this kind of stuff is Manipulation section..Why cant you use power query?It will take just 1 min to achieve this.Assume that, you are bound to write a measure.Then only, you can create a supporting table and after that you can pull out the value by dax and then you can hide this table from report view.see your file, what i have done.

https://dropmefiles.com/AhgMB
Hope you will get some help from this way.

 

I am tagging @Greg_Deckler ..Hope he will guide you more efficiently.

Regards,

Sanalytics

 

@sanalytics , ty for yours recomendations.

 

As an end result, this is what I need. But i can't use power query cause i get Statistics table from web service, its have over 2 bill rows and updated every day. In the example which I dropped only one sensor, in a real request 102 sensors, so i need to created support table in each sensor.

 

Bessonnizza1992_0-1601828982263.png

 

Hi @Bessonnizza1992,

I can use the following measure formula to reproduce the chart as your snapshot displayed.

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 )
    )

How did you really table structure like? Can you please share some dummy data with multiple sensors then we can test to coding formula on it?

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft , ty for reply!

 

All is working fine, but a little bit slow.

Bessonnizza1992_0-1602577461131.png

 

Can you explain me how can i filtered this rows? Only the current line (with original value) and previous line needs to be left. The end result should look like:

Bessonnizza1992_1-1602578024493.png

 

 

Hi @Bessonnizza1992,

My formula is included iterator calculation, if you are handling huge amount of records, it may reduce the performance.

>>Can you explain me how can i filtered this rows? Only the current line (with original value) and previous line needs to be left. 

I'm not so clear about your requirement, can you please explain more about this? Did you mean the expression should end the calculate if they executed to the end of your fact table records?
If this is a case, you can add an if statement to package this expression to prevent the calculation on not existed date range:

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Any ideas?

Resolver IV
Resolver IV

@Bessonnizza1992 ,
Please try below formula,
Min value =

SUM('Table'[Min of value])
 
Filldown= 
VAR _1= MAX('Table'[Time])
RETURN
CALCULATE([Min value],TOPN(1,FILTER(ALL('Table'),'Table'[Time] <=_1 && NOT(ISBLANK([Min value]))),'Table'[Time],DESC))

Hope,it will works..
 
regards,
sanalytics

Ty for reply, @sanalytics 

 

Tried yours measure, but power bi froz and couldn't load so much data. CalendarTime table have 86400 rows. I have an idea with another measure:

 

_DiscreteSensor / PreviousValue = 
VAR _1 =
    MAX ( 'CalendarTime'[Index] ) - 1
RETURN
    CALCULATE (
        MAX ( StatisticsDiscreteSensors[Value] ),
        FILTER ( ALL ( 'CalendarTime' ), CalendarTime[Index] = _1)
    )

 

All is ok, but measure return return values in incorrect time row. For example:

 

Timestamp - value - previous value

05:53:40 - blank - 0 / timestamp must be 07:00:03

07:00:04 - 0 - blank

07:00:05 - blank - 0 / timestamp must be 08:02:58

08:02:59 - 0 - blank

 

Bessonnizza1992_0-1601763568180.png

 

Do you know how can i do it?

@Bessonnizza1992,
can you please provide us some dummy data and your exact output so that, we can try..it is very difficult to assume your exact requirement by a screenshot.

Regards

sanalytics

@sanalytics, hi again.

 

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

 

TimeStamp here must be:

00:00:00

00:04:59

00:05:00

00:41:59

 

Bessonnizza1992_0-1601805208315.png

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors