Implementing DAX Measure Groups Into Your Reports – A Power BI Modeling Review

3 comments

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.

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.

sample Power BI modeling report

You’ll see that under the FIELDS pane, there is a number of measure groups or measure tables.

DAX measure groups within FIELDS pane in Power BI

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.

DAX measures within measure 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.

Key measures for Power BI modeling

Then, I branch out into these selected analytical patterns such as Moving Averages, Ranking, Scenario Analysis, Segmentation Techniques, Time Comparison, etc

Other DAX measure groups that contains analytical patterns

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

Time Comparison measure group within the FIELDS pane

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.

Placing measure tables at the right side of the data 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.

The search feature within Power BI Models

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.

Sample search results using the keyword 'moving'

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.

selecting through visualizations within a Power BI Model

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.

creating a measure group or table within a Power BI model

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.

adding name for your measure table or group

Then, it will create a table. It won’t build any relationship because you don’t actually have any data in here.

Adding a new measure table within a Power BI model

What you need to do next is to create a measure by clicking the New Measure option within the Home Tab.

adding a new measure within the measure table or group

Let’s call this measure Demo Sales. We’ll reference another measure (Sales LY) just to quickly create one.

adding sample DAX measure

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. 

clicking the show hide pane within Power BI

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

membership banner 3

***** 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….

Enterprise DNA Events


3 comments on “Implementing DAX Measure Groups Into Your Reports – A Power BI Modeling Review”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.