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
wimsangers
Helper I
Helper I

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?
 
1 ACCEPTED SOLUTION

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. 

View solution in original post

5 REPLIES 5
AsMoBhosca
Frequent Visitor

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 

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. 
MattAllington
Community Champion
Community Champion

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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
Not applicable

Hi,

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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