Earlier function with large dataset

[Esmee]

Hi!

I have a large dataset about 3M records which looks like this: 

campaignID	persoon	date	amount	number
1	a	3-6-2016	1	0
1	b	3-6-2016	1	1
1	c	3-6-2016	1	0
2	a	1-1-2017	1	0
2	i	1-1-2017	1	0
2	d	1-1-2017	1	1
3	a	1-4-2018	1	0
3	b	1-4-2018	1	1
3	e	1-4-2018	1	0
4	f	1-5-2015	1	1
4	g	1-5-2015	1	0
4	h	1-5-2015	1	1
4	b	1-5-2015	1	0

I would like to achieve the following: 

How many campaigns did the person recieve before the current (selected) campaign? 

So for example if I select campaign 3 my outcome would be:  

person a received 2 campaigns

person b received 2 campaigns

person e received 0 campaigns

When I am using this small dataset the following dax function works: 

Column  = CALCULATE(sum(Blad1[amount]);FILTER(Blad1;Blad1[date]<EARLIER(Blad1[date]) && Blad1[persoon]=EARLIER(Blad1[persoon]))) 

Measure = var calc = sum(Blad1[Column]) return if(ISBLANK(calc);0;calc) 

But when I use these functions in my large dataset it runs out of memory. 

Do you have any suggestions how I can get my desired outcome for the large dataset?

[v-shex-msft]

Hi @Esmee,

The performance is due to your formula, for calculated column it will calculate through 1+2+3+n(row count) rows. Then your measure will duplicate these operations.(1+ 2+1 + 3+2+1 + 4+3+2+1...) It obviously will cause performance issue.

I haven't find any effective solutions to improve performance, maybe you can try to use below measure if it works.

Measure =
VAR _currentDate =
    MAX ( Blad1[date] )
VAR _currentPersion =
    SELECTEDVALUE ( Blad1[persoon] )
VAR _currentCampaign =
    SELECTEDVALUE ( Blad1[campaignID] )
RETURN
    CALCULATE (
        SUM ( Blad1[amount] );
        FILTER (
            ALLSELECTED ( Blad1 );
            Blad1[date] < _currentDate
                && Blad1[persoon] = _currentPersion
                && Blad1[campaignID] < _currentCampaign
        )
    )

Regards,

Xiaoxin Sheng

[Esmee]

Thank you for your suggestion!

I tried to use the measure and adjusted it a bit: 

measure = CALCULATE (

        SUM ( Blad1[amount] );

        FILTER (

            ALL(Blad1 );

           Blad1[date] < max(Blad1[date])

              && Blad1[persoon]=SELECTEDVALUE(Blad1[persoon])

        ))

It works but only if I include the persoon in the table:

campaign	measure	persoon
3	2	a
3	2	b
1	1	b
2	1	a

What I would like is to create a table like this: 

campaign	measure
3	4
1	1
2	1
4	0

But when I remove the persoon the table gives no value. 

I hope you can help me!

[v-shex-msft · Accepted solution]

Hi @Esmee,

Please remove 'person' filed and try to use below formula:

measure =
CALCULATE (
    SUM ( Blad1[amount] );
    FILTER (
        ALL ( Blad1 );
        Blad1[date] < MAX ( Blad1[date] )
            && Blad1[persoon] IN VALUES ( Blad1[persoon] )
    )
)
    + 0

Regards,

Xiaoxin Sheng

[Esmee]

Thanks! It works!