Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Cumulative on a Very Large Dataset with Slicers

Hi there,

 

So, I have been stuck in a cumulative calculation due to the size of my dataset. Here is a sample of the dataset.

 

ID

Users

ActionType

InteractionType

App

7981f340-5e00-49ec-ab55-1cc975cbc5ce

         3,946,783,049

Browse

Right Click

A

b29125a7-a0bc-489c-a739-52c4c3ba1fcf

         1,092,345,660

Browse

Hover

D

be89f247-60f3-455e-b0a7-cd6128bc3f9c

         1,734,750,135

Transaction

Left Click

A

b1c9c5a3-e75c-431e-8b85-bc2d74ba730d

         1,562,373,384

Transaction

Right Click

C

3f9f1dd8-0fda-4c48-94d2-bf779ff95226

         1,107,690,317

Browse

Right Click

B

d0a66a39-3d57-42c5-b2b7-45a54da598a1

             648,011,451

Browse

Right Click

C

c7d7572b-dbb2-4074-985f-4cc44b0a1b51

             230,762,581

Transaction

Left Click

B

a929cead-16ba-49e2-b389-6728af4b5f3d

             228,863,834

Transaction

Left Click

B

08f66143-d1d8-4298-9fbf-ce0eee0e8a62

             216,938,967

Browse

Right Click

D

ef615efa-5892-4b85-90a8-c6718d23e05c

             206,676,175

Transaction

Hover

A

 

My goal is to add Users column cumulatively in descending order (as shown below), while having columns ActionType, InteractionType and App as slicers with multi-select i.e. this calculation becomes dynamic per each slicer combinations.

 

ID

Users

CumulativeUsers

7981f340-5e00-49ec-ab55-1cc975cbc5ce

   3,946,783,049

           3,946,783,049

be89f247-60f3-455e-b0a7-cd6128bc3f9c

   1,734,750,135

           5,681,533,184

b1c9c5a3-e75c-431e-8b85-bc2d74ba730d

   1,562,373,384

           7,243,906,568

3f9f1dd8-0fda-4c48-94d2-bf779ff95226

   1,107,690,317

           8,351,596,885

b29125a7-a0bc-489c-a739-52c4c3ba1fcf

   1,092,345,660

           9,443,942,545

d0a66a39-3d57-42c5-b2b7-45a54da598a1

       648,011,451

         10,091,953,996

c7d7572b-dbb2-4074-985f-4cc44b0a1b51

       230,762,581

         10,322,716,577

a929cead-16ba-49e2-b389-6728af4b5f3d

       228,863,834

         10,551,580,411

08f66143-d1d8-4298-9fbf-ce0eee0e8a62

       216,938,967

         10,768,519,378

ef615efa-5892-4b85-90a8-c6718d23e05c

       206,676,175

         10,975,195,553

 

I have used this DAX measure to calculate CumulativeUsers column above:

 

CumulativeUsers = 
VAR __minUsers = MIN( SampleDataset[Users] )

VAR __cumulativeUsers = CALCULATE( SUM( SampleDataset[Users] ),
                        FILTER( ALLSELECTED(SampleDataset[Users] ), 
                        SampleDataset[Users] >= __minUsers) )

RETURN __cumulativeUsers

 

I am getting the correct answer with this, but the only issue I face is, since I have more than 1 million distinct IDs in my dataset, as soon as I pick a slicer combination (from columns ActionType, InteractionType and App), the report halts and eventually my system crashes due to RAM overflow. It only works for those slicer combinations which have very little data for that particular combination. I have also tried this in systems with total RAM as large as 64GB and got the same response.

 

So, did I do something incorrectly or inefficiently? Or did I go beyond the supported data limits of Power BI here?

 

Your help or suggestions would really be appreciated. Thanks in advance.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , This formula is good. See if there are any bi-directional join. If you can make them single directional. That would help

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , This formula is good. See if there are any bi-directional join. If you can make them single directional. That would help

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Hi @amitchandak ,

 

Thank you for responding promptly. I checked all the joins used in our datasource(Power BI Model), as per your suggestion. They were all single directional only to my dismay.

I have tried to limit the data as much as I could over at the SQL view side (while maintaining the dynamicity needed for the current requirement), but its still not good enough to avoid the system crash when using various slicer combinations. 

At this point we are thinking of a parameterized stored procedure approach, that takes care of the cumulative logic based on slicers combinations being sent as parameters. The only issue I could foresee there is the added report response delay and potential loss of multi-select slicer functionality. I read somewhere in these forums that the feature to send mutliple selections per parameter is yet to be added in Power BI.


Please correct me anywhere here or if we can take up a better approach to mitigate this issue.

 

Thank you.  

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.