Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Optimize a Running Total

Hello community,   I manage the data of an app and would need some help to optimize the classical running total    Sign up count =            CALCULATE (            DISTINCTCOUNT( 'Global table...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    Try to change you measure to make the filter on the Global Table [Date]

     

    Sign up =
CALCULATE(
          COUNTROWS( 'Users Info' );
          FILTER( 'Users Info'; 'Users Info'[Sign_up] <= MAX( 'Global Table'[Date] )
           )
)

    Then place the 'Global Table'[Date] on the x-axis should work as expected.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
8 years ago

Hi Anonymous,

 

You can have a better performance making an adjustment to your current formula. Having a ALL statement for the full table you will get all columns and all rows try to only add the columns you need to interact with:

Sign up count =
           CALCULATE (
           DISTINCTCOUNT( 'Global table'[email] );
           FILTER(  ALL(  'Global table'[date] ); 'Global table'[date] <= MAX( 'Global table'[date] ) )
                       )

This should work faster, try it and then tell me the result.

 

Regards,

MFelix

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey MFelix,

     

    Thank you for your answer, selecting only one column is indeed better but I is still taking ages to load the graph (10-20 secs) whereas it is alone in a page... And still crushes my poor RAM and even power BI cloud returns error message as it takes too long to load.. 

     

    I feel I'm very close to getting something with the summarized table, the calculation is performed well when someone signed up during the week and I just have to return a value when nobody signed up in the week...

    Here is a visual example of my issue... A Company where we launched our product early in march (that explains the peak) and who then had quite a few additional users so most weeks are filled, in some cases, no one installed during the week and the result returned is blank.

     

    The Date axis which I take from 'Users Info'[Sign_up] with a 7 days group (for weeks) is a bit of a cheat but not really viable, I feel like I have to user the date from 'Global Table'[Date] which I use in most graph and works but it returns this:

     

     

    A not so running total yet some regular DISTINCTCOUNT for each week....

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi Anonymous,

       

      Try to change you measure to make the filter on the Global Table [Date]

       

      Sign up =
CALCULATE(
          COUNTROWS( 'Users Info' );
          FILTER( 'Users Info'; 'Users Info'[Sign_up] <= MAX( 'Global Table'[Date] )
           )
)

      Then place the 'Global Table'[Date] on the x-axis should work as expected.

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hey MFelix,

         

         

        Wow... Quite frustrating as I've spent a day scooting the internet to see if I could find someone with the same pattern.... I feel a bit dumb.. 

         

        Thanks so much it worked, I have to precise for the people who might encounter the same pattern I had to remove the Bi-directionnal Cross-filtering between the two table (Users Info & Main table). Now it's one way: Users Info -> Main Table.

         

         

        Thanks Felix,

        L