Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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!
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
Thanks! It works!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |