Forum Discussion

Esmee's avatar
Esmee
Frequent Visitor
8 years ago
Solved

Earlier function with large dataset

Hi!

 

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

 

campaignIDpersoondateamountnumber
1a3-6-201610
1b3-6-201611
1c3-6-201610
2a1-1-201710
2i1-1-201710
2d1-1-201711
3a1-4-201810
3b1-4-201811
3e1-4-201810
4f1-5-201511
4g1-5-201510
4h1-5-201511
4b1-5-201510

 

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?

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Esmee
      Frequent Visitor
      8 years ago

      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:

       

      campaignmeasurepersoon
      32a
      32b
      11b
      21a

       

       

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

       

       

      campaignmeasure
      34
      11
      21
      40

       

       

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

       

      I hope you can help me!

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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