In this tutorial, I’ll be talking about a really important topic regarding Power BI modeling and organization. In most of the demos that Enterprise DNA releases across webinars, summits, and to Enterprise DNA members, you will see that the use of measure groups is extensive. You may watch the full video of this tutorial at the bottom of this blog.
There is a very good reason why measure groups have been implemented in all the models that Enterprise DNA creates. When you develop advanced analytical models and reports inside Power BI, organizing all the DAX measures you are creating is of extreme importance.
If you do not do this, your model will be very difficult to maintain and audit in the future because measures will be hard to find.
This tutorial is a deep dive into why measure groups are so important and how you can actually create them inside Power BI. This is certainly a relevant and a valuable best practice if you’re just starting out and getting an introduction into measures.
Let’s try to look at this report here. This came from the February, 2018 Learning Summit.
You’ll see that under the FIELDS pane, there is a number of measure groups or measure tables.
The development side of the learning summit was only three sessions of about 45 minutes up to an hour. And within that time, we were able to create about 40 to 50 measures within these unique groups.
Imagine if you have these within all your tables and you won’t organize them properly. You’ll definitely get lost in terms of what you’re actually doing in your development.
Measure Branching Methodology
Measure branching is another important development technique that builds upon measure groups.
In this methodology, I usually segregate the measures and put them into groups based on what branch they are in. Now, I always start with the core calcs (calculations), and I call these Key Measures.
This is where I most likely start off. I create all the simple DAX measures such as SUM, AVERAGE, SUMX, and all the other simple core functions. I put all those simple, iterating, and aggregating functions into the Key Measures group.
Then, I branch out into these selected analytical patterns such as Moving Averages, Ranking, Scenario Analysis, Segmentation Techniques, Time Comparison, etc.
As you can see, even your Time Comparison measure group can have various measures. This is because you might not just want to calculate Sales Last Year, but also all the differences between Sales This year and Last year. Furthermore, you might want to calculate the percentage change between this year and last year.
Now, those are just some instances on how you can get these numerous measures. And you need to keep it really intuitive so that you can reference these measures and drag them into your reports and visualizations.
This is to avoid searching through all of your tables (trying to find which measure goes where), and getting confused.
You can easily sort all these measures, which can make a huge difference in terms of your speed and efficiency in creating reports.
Sample Technique For Power BI Modeling
Now, I’ll share what I usually do on the model. This is deemed important from an organization perspective as well.
I usually place my measure tables over to the right side. Sometimes, I also place them at the bottom part. This is because they don’t generally have a relationship with our core model.
However, referencing them within this section is still essential. This is because we want them to be in one clear group where they are easily identifiable.
In this case, you can easily check and find all your ranking formulas, which are within the Demand Scenarios group.
And that’s solely the key thing that I wanted to point out here. To reiterate, I want to mention just how important these measure groups are when it comes to the efficiency of Power BI modeling and organization.
Utilizing The Search Feature Within Power BI Models
Moreover, you can take advantage of other features like the search bar within the FIELDS pane.
Let’s try to type ‘moving’. As you noticed, it displayed all the relevant results within a certain measure group which is connected to what we have searched.
Selecting Through Visualizations
Here’s another cool thing that I want to show you. When you select on visualizations (in this case the City Highlights), you can always see where the included measures appear within our measure groups or tables.
It’ll certainly be easy for you to understand how things were set up or posted even by someone else. And in the future, when you might want to look at your report, you’ll be able to quickly find and reference these measures.
That’s mainly the idea that I want to pinpoint here. But before we end this, I’ll be showing you how to actually create a measure group.
Creating A Measure Group In A Power BI Model
To create a measure group, just click the Enter Data option within the Home Tab.
Then, a table window will appear. You don’t actually need to put anything inside of the table. This means you can leave it blank.
Just type in the name of your measure group. In this example, we’ll name it as ‘Demo’. Afterwards, click Load.
Then, it will create a table. It won’t build any relationship because you don’t actually have any data in here.
What you need to do next is to create a measure by clicking the New Measure option within the Home Tab.
Let’s call this measure Demo Sales. We’ll reference another measure (Sales LY) just to quickly create one.
So, the created Demo Sales measure has now appeared within the Demo measure group.
Let’s delete this Column1 first, because we won’t actually be needing it.
After deleting it, just click the Show/Hide pane in and out, and you’ll see that this is now a measure group or a measure table.
So, that’s just to quickly show how you can actually create a measure group.
Conclusion
Measure branching is an incredibly vital development concept to start implementing in your models as much as possible. By utilizing this development technique in combination with measure groups, you can absolutely bring great scale and efficiency to the development work you are implementing inside Power BI.
Enjoy reviewing this particular content. It will immeasurably improve your Power BI modeling and reporting skills inside Power BI. I highly recommend implementing these ideas and techniques into your own reports and models.
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
[youtube https://www.youtube.com/watch?v=HDsncpjpM3E?rel=0&w=784&h=441]
***** Related Links *****
How To Organize Your Power BI Data Model – Best Practice Tips
Power BI Financial Reporting: Allocating Results To Templates At Every Single Row
Procurement Decisions Model And Analysis In Power BI
***** Related Course Modules *****
Power BI Showcases
Power BI Resource
Ultimate Beginners Guide to Power BI
***** Related Support Forum Posts *****
Measure Groups In Directquery Mode
Creating Groups From A Measure
DAX – Grouping Not Return Correct Total Value
For more measure groups support queries to review see here….
