During the Feb 27 Guy in a Cube live stream, I submitted a two-part question to gather thoughts on Report Level Measures.

Q: When separating the model from report files, where is the best place to keep measures that are unique to the report? For example, conditional format, dynamic text boxes. 1 / 2

Q: Do you recommend a naming standard in case the measure should move into the data model to be shared? 2/ 2

TL/DR
You can watch the Live Stream Replay to hear Patrick’s answer. That link will take you directly to the question and answer. You might also want to read the chat replay that provides additional comments.

The Background

Our team consists of globally dispersed developers, currently the US, India, and New Zealand. We also separate the development roles into Enterprise Data Warehouse (EDW) backend developers, Semantic Tabular Modelers, and Power BI Report developers.  When building reports off a Tabular model, either a Power BI dataset or Analysis Services (AAS / OnPrem), measures can be built into the report instead of the central model. The report development time can be reduced using this approach.  Especially during a proof of concept phase when you might be trying out different visualization ideas.

The Pros and Cons

As discussed in the answer, the best reason to keep the measure in the central model is to reuse them across reports. The measures implementing business logic requirements must provide the same results on every report; therefore, they must be in the central model.  The same goes for poor-performing measures due to the incorrect implementation. The central model allows you to improve those measures when new techniques are discovered and implemented, especially when a model design improvement is needed.

Matthew’s comment applies to many areas within Power BI. We hear it mentioned when discussing importing data through Power Query. It certainly applies here as well. In my experience, the Semantic Tabular Modelers are better at DAX than the Report Developers. This does not mean the Report Developers produce incorrect/bad DAX, they usually are learning and will continue to improve those skills. They also work closely with the report consumers’ feedback and develop brilliant user experience ideas—this needs to be encouraged and demoed to the entire team and the report consumers. When creative people collaborate, the result is a better deliverable.

We have been using report-specific measures but have run into the issue mentioned in the answers. We found the same measure in multiple reports and have had some measures perform poorly. We needed to balance out our process better.

The Approach

Mathew again summarizes the thoughts on this very well.

The challenge comes when moving those measures into the central model while keeping the report working correctly. Thomas hits directly on point. When the central model and report have the same measure name, the report will error.

Naming Standards

We are implementing a naming standard for any measure produced in the report file. We decided to prefix them with an underscore, “_”. This allows us to identify them quickly, and when moved into the central model, the prefix is removed. The report will continue to work and can be migrated to use the central model measure the next time report changes occur. The main downside I have found to this approach is the need to rename the measure on each visual. The other downside currently is you cannot use Tabular Editor to edit/review the report file measures. There is no model in a report file when using a live connection—using Direct Query for live connections once it is GA could address this issue in the future.  

Our Software Development Life Cycle (SDLC) includes review gates to identify the measures that can potentially move and then schedule Modeler time to implement them. The report review gate also identifies the measures that can now be switched, that work is completed before the report changes are released. Also, during scheduled technical debt reduction tasks, the reports that have not changed due to requirement changes are reviewed to switch to the central model measures. I am sure there is another way of accomplishing this and improvements to this approach. I look forward to continuing the discussion on Twitter.

When you separate the data model from reports in #PowerBI, do you create measures in the report? This was discussed on the @GuyInACube live stream. I have a follow up blog to continue the conversation – https://t.co/i7v0ETDqG4

— Kevin Arnold (@kevarnold) March 1, 2021