Forum Discussion

jschoot's avatar
jschoot
Regular Visitor
5 years ago
Solved

Simple Difference

Hello,

 

I am trying to make an report on how many people have been in a building. I have 3 counters and they count the amount of people that go in and out per 15 minutes. I am trying to get the difference per counter per timestamp per direction. I have something that works but is really slow/consumes a lot of CPU and memory on large amount of data.

 

 

 

 

Diff = Var baseFilter = FILTER('Blad1','Blad1'[Device Name] = EARLIER('Blad1'[Device Name]) &&'Blad1'[Counter Index]=EARLIER('Blad1'[Counter Index]))
        var selectDate = CALCULATE(MAX('Blad1'[Timestamp]),baseFilter,  FILTER(baseFilter, 'Blad1'[Timestamp]<EARLIER('Blad1'[Timestamp] )))
        var v='Blad1'[Value] - CALCULATE(sum('Blad1'[Value]),baseFilter, FILTER(baseFilter, 'Blad1'[Timestamp] =selectDate))
    return 
           if(selectDate=BLANK(),BLANK(), if(v <0, 'Blad1'[Value],v))

 

 

 

 

 

I am guessing that the EARLIER function is the bottleneck

 

I am looking for a simpler/faster solution to this.

The data: Difference.pbix 

Need Help
Show and Tell
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi jschoot ,

     

    52000 records should be the issue, the formula will work for each rows and may filter all rows in calculation of each row(even though the VertiPaq engine will optimize this part of the calculation , but it is still a heavy work). And there is no aggregate function in your formula.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jschoot , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi jschoot ,

     

    Would you please try to use the following calculated column:

     

    Diff =
VAR DN = Blad1[Device Name]
VAR CI = Blad1[Counter Index]
VAR TS = Blad1[Timestamp]
VAR selectDate =
    CALCULATE (
        MAX ( Blad1[Timestamp] ),
        FILTER (
            Blad1,
            Blad1[Device Name] = DN
                && Blad1[Counter Index] = CI
                && Blad1[Timestamp] < TS
        )
    )
VAR V =
    Blad1[Value]
        - CALCULATE (
            SUM ( Blad1[Value] ),
            FILTER (
                Blad1,
                Blad1[Device Name] = DN
                    && Blad1[Counter Index] = CI
                    && Blad1[Timestamp] = selectDate
            )
        )
RETURN
    IF ( selectDate = BLANK (), BLANK (), IF ( v < 0, 'Blad1'[Value], v ) )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

    • jschoot's avatar
      jschoot
      Regular Visitor
      5 years ago

      Thank you v-deddai1-msft,

       

      It works, is easier to read in my opinion but it is not faster nor less memory consuming. Could this be caused by the amount of records? 52000 records should not be many for PBI in my opinion..

       

      Best regards,

      Joris

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi jschoot ,

         

        52000 records should be the issue, the formula will work for each rows and may filter all rows in calculation of each row(even though the VertiPaq engine will optimize this part of the calculation , but it is still a heavy work). And there is no aggregate function in your formula.

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

        Best Regards,

        Dedmon Dai