09-01-2016 01:42 AM
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?
09-01-2016 02:29 AM
@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.
09-01-2016 11:09 AM
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?
09-02-2016 01:41 AM
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.
09-12-2016 03:49 AM
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?
09-15-2016 12:19 PM
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.