Forum Discussion

Bessonnizza1992's avatar
Bessonnizza1992
Helper I
5 years ago

pull values ​​down

Hello all!   How to pull values ​​down, need measure, not PQ.    
Need Help
Bessonnizza1992's avatar
Bessonnizza1992
Helper I
5 years ago

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.

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • Bessonnizza1992's avatar
    Bessonnizza1992
    Helper I
    5 years ago

    Anonymous , ty for reply!

     

    All is working fine, but a little bit slow.

     

    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:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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