Simple Difference

[jschoot]

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 

[v-deddai1-msft]

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]

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

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

[amitchandak]

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

[jschoot]

@amitchandak  I have edited the post and added the link to a pbix file.

[jschoot]

@amitchandak I found that the download link did not work as expected. Now it should work.