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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paulalmond91
Helper II
Helper II

Scheduled Refresh Breaks my Measure

Hi There,

 

I've built a dashboard that gets data from a series of REST API calls. I've created measures that leverages this data to work out figures such as profit, costs, margins etc.

 

When I publish this dashboard from Power BI Desktop the measures are all showing correctly and forcing a manual refresh doesn't break the measures and it is displaying correctly.

 

However, when the next scheduled data refresh takes place the measure is reset to blank. This only breaks one of my measures so I cannot determine its cause.

 

I can't provide access to the dashboard due certain data being private but the measure is:

Total Cost = SUMX(CALCULATETABLE('Open Programme Events', 'Open Programme Events'[Is Cancelled?]="false"), ('Open Programme Events'[Active Delegates] * RELATED('Course Costs'[Per Student Fees]) + ('Open Programme Events'[Active Delegates] * RELATED('Course Costs'[Registration (Per Del)]))) + RELATED('Course Costs'[Per Event Fees]) + [Tutor Fees])

 

This measure continues to work following a scheduled refresh:

Targetted Income = CALCULATE((SUM('Open Programme Events'[Course Price]) + SUM('Open Programme Events'[Registration])) * SUM('Open Programme Events'[Maximum Delegates]))

 

Does anyone have any experience with this that can advise why the measure would be breaking?

 

Thanks!

5 REPLIES 5
paulalmond91
Helper II
Helper II

So it turns out that the original solution solved the problem to one measure but it is infact the related() function that doesn't appear to work with scheduled refresh.

 

Can anyone confirm if they've had similar problems or if they know a way around not using the related() function?

The only way that I can imagine scheduled refresh breaking a measure that uses RELATED() or similar is that the data refresh violates relationship constraints. I.e. what was a 1:N relationship before the refresh is turning into N:N after refresh.

paulalmond91
Helper II
Helper II

Hi Both,

 

Thanks for your feedback! Apologies for the poor format I'm just getting into Dax formatting so will use the formatter going forward.

 

I think I've worked out a solution.

 

I did as you recommended ankitpatira, and split the related measures into separate measures. Intead of now using Related() it calls in the measure.

 

This seems to have fixed the issue I was having and my dashboard is now refreshing correctly.

 

Thanks Again!

ankitpatira
Community Champion
Community Champion

@paulalmond91 As a starting point can you break down that measure into two or three measures to be able to figure out what is causing it to break. So maybe have related() function in a seperate measure and other parts in other measure and see which one of them breaks during refresh. 

1. http://www.daxformatter.com is your friend.

1.b. Code blocks in the forums are your friend.

 

2. Your measure [Targeted Income] does not need a CALCULATE().

 

3. It looks like your SUMX() is referencing another measure [Tutor Fees]. Can we see the definition of that?

 

4. I have to assume that 'Course Costs' is on the 1 side of a 1:N relationship with 'Open Programme Events'. Is this the case?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors