Forum Discussion

wimsangers's avatar
wimsangers
Icon for Helper I rankHelper I
7 years ago
Solved

Count cumulative with inactive relationship

Hi all,

 

I am trying to calculate a cumulative measure with an inactive relationship.

This is the formula I use. 

 

charging points = calculate(COUNTA(ChargePoints[ExternalID]);USERELATIONSHIP(ChargePoints[Created];DIM_Calendar[Date]);filter(ALLSELECTED(ChargePoints[Created]);ChargePoints[Created] <= MAX(ChargePoints[Created])))
 
When I plot this I am getting the total each month, but not the cumulative number.
How can I fix this?
 
Need Help
  • wimsangers's avatar
    wimsangers
    5 years ago

    Hi AsMoBhosca ,

     

    It is already a long time ago, but I think I managed to fix the problem thanks to the tip of MattAllington.

    If you look at the code of my original problem this is the updated version:

    calculate(COUNTA('bi chargecard_assignment'[evco_id]),filter(ALL('Calendar for cards'[Date]),'Calendar for cards'[Date] <= MAX('Calendar for cards'[Date])),'bi chargecard_assignment'[assigned_until] <> BLANK(),USERELATIONSHIP('Calendar for cards'[Date],'bi chargecard_assignment'[assigned_until])) + calculate(COUNTA('bi chargecard_assignment'[evco_id]),filter(ALL('Calendar for cards'[Date]),'Calendar for cards'[Date] <= MAX('Calendar for cards'[Date])),'bi chargecard_assignment'[assigned_until] > TODAY(),USERELATIONSHIP('Calendar for cards'[Date],'bi chargecard_assignment'[assigned_until]))
     
    Hope it helps you. 

5 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    7 years ago

    You need 2 nested calculate functions. The outer calculate should set the USERELATIONSHIP.  The inner one does the cumulative total. 

    • wimsangers's avatar
      wimsangers
      Icon for Helper I rankHelper I
      7 years ago

      Hi MattAllington ,

       

      Thank you for your fast reply.

      Could you maybe help in writing this formula.

      I am now getting the error that I cannot use a calculate function in a true/false expression.

      This is my formula.

       

      Charging points = calculate(USERELATIONSHIP(ChargePoints[Created];DIM_Calendar[Date]);calculate(COUNTA(ChargePoints[ExternalID]);filter(ALL(ChargePoints[Created]);ChargePoints[Created] <= MAX(ChargePoints[Created]))))
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

         

        Have you solved your problem? I have a similar problem.

  • AsMoBhosca's avatar
    AsMoBhosca
    Frequent Visitor
    5 years ago

    Hi,

    wimsangers 
    Did you ever figure out how to correctly nest the calculate functions? I am also trying to get a cmulative count using an inactive relationship.
    Thanks 

    • wimsangers's avatar
      wimsangers
      Icon for Helper I rankHelper I
      5 years ago

      Hi AsMoBhosca ,

       

      It is already a long time ago, but I think I managed to fix the problem thanks to the tip of MattAllington.

      If you look at the code of my original problem this is the updated version:

      calculate(COUNTA('bi chargecard_assignment'[evco_id]),filter(ALL('Calendar for cards'[Date]),'Calendar for cards'[Date] <= MAX('Calendar for cards'[Date])),'bi chargecard_assignment'[assigned_until] <> BLANK(),USERELATIONSHIP('Calendar for cards'[Date],'bi chargecard_assignment'[assigned_until])) + calculate(COUNTA('bi chargecard_assignment'[evco_id]),filter(ALL('Calendar for cards'[Date]),'Calendar for cards'[Date] <= MAX('Calendar for cards'[Date])),'bi chargecard_assignment'[assigned_until] > TODAY(),USERELATIONSHIP('Calendar for cards'[Date],'bi chargecard_assignment'[assigned_until]))
       
      Hope it helps you.