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 di...
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

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