❕ Note - The new model explorer is currently in preview as part of Power BI Desktop October 2023 release. To enable the preview feature go to File -> Options and settings -> Options -> Preview features and enable "Model explorer and Calculation group authoring" The Model Pane feature is showcased here as part of Power BI Desktop's October 2023 release. Power BI October 2023 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI
The Model Pane in Power BI Desktop provides a clear view of the objects within your semantic model. It organizes different model objects in a way that makes it easy to navigate and work with all objects within your model. This article will explore the various features of the Model Pane. It will highlight how it helps you to interact with your semantic model and author objects like relationships and calculation groups. By exploring the Model Pane, we’ll see how it makes data model authoring simpler and more efficient in Power BI Desktop.
At the time of writing, objects available in the Model Pane are:
- Semantic model
- Calculation groups
- Calculation group
- Calculation item
- Calculation groups
- Cultures *
- Measures
- Perspectives *
- Relationships
- Roles
- Tables
- Columns
- Hierarchies
- Measures
- Partitions *
* Can’t be authored in Power BI Desktop
Semantic model
The semantic model is the root level of what was previously called a Power BI dataset. The term semantic model was first introduced in Power BI in the October release of Power BI Desktop as part of preview of the new model explorer.
However, the word semantic model is not new in any way but has been around since the 70s. Basically, it has a broader meaning than just a simple dataset. A semantic model is the idea of having a layer that acts as a bridge between the physical data storage and the end application or users.
In the world of Power BI, we have a dataset (collection of tables) as a base. On top of this we have definitions on how data is related (relationships, who is allowed to see what (security rules), and business definitions that makes sense of the data to end user (measures). On top of this, the semantic layer provides both easier access to the data and increased performance when it comes to querying large amounts of data, compared to many original operational source systems or file formats. A semantic model (and also Power BI’s Semantic model) will help in navigating and make use of the data in your organization. A semantic model will also have huge benefits when using a centralized data warehouse or data Marts, because it can act as a single point of entry to your data from within your organization.
Despite storing base metrics for data in data marts, there are some calculations that is very hard or practically impossible to implement in a static table. For example, semi-additive or percentage-of-total calculations.
Time intelligence calculations also adds some challenges. Especially when calculations need to use input from end user filtering in reports. The other option is to pre-calculating all variations for future use in your data mart. And that is not a viable option.
Furthermore, reporting tools do not know how metrics from your fact table should be calculated. For example, should it summarize, count, take the average or something else? This is something we can define in the semantic layer.
Model pane in Power BI Desktop
The Semantic model object in the Model Pane in Power BI Desktop allows us to enter a description for the model. It also allows us to see the server name, compatibility level, cultures and an option to set Discourage implicit measures to either Yes or No.
Server name
Exposing the server name of the local instance of analysis services is a nice feature. It allows for connecting to the underlying database using e.g. SQL Server Management Studio (SSMS) where you can execute queries and script objects.
Compatibility level
The compatibility level shows the version of the analysis services tabular model. You can not change or define the compatibility level in Power BI Desktop. The compatibility level can be good to know in order to understand different behavior of features in the semantic model.
Cultures
Cultures is a property that indicates the language of the semantic model. They are used to define e.g. formatting rules within the model and are also used in translations. Currently you can not author cultures in Power BI Desktop. However, you are able to modify them using External tools such as Tabular editor.
Discourage implicit measures
Discouraging implicit measures does exactly what the name suggest. The use of implicit measures is discouraged when you set this to true. When it comes to Power BI Desktop, this has the effect that using implicit measures is blocked.
Implicit measures needs to be discouraged in order to create calculation groups in a Power BI semantic model. This means that if your semantic model has one or more calculation groups, all measures must be explicitly created in the model and cannot be implicit.
Calculation groups
Calculation groups is a feature in Analysis Services Tabular that was introduced in 2019. Up until today, creating calculation groups in Power BI Desktop has only been possible by using external tools, a feature released in July 2020. Creating calculation groups in Power BI Premium has been available through the XMLA endpoint prior to this using external tools such as Tabular Editor.
Calculation groups can modify the calculations of existing measures, allowing for the creation of varied versions of a single measure across a defined set of values, also known as calculation items.
One example for using calculation groups can be to limit the number of measures in time intelligence calculations.
Let’s say that you have a measure for sales in your model and you want to create various time intelligence calculations for sales. For example, you might want to have calculations for year to date, month to date, previous year, year over year, year over year % etc. The list can potentially be very long.
Now let’s think about if you also want to create similar calculations for other measures such as Cost, Profit, Number of customers, Budget and so on.
As you probably understand the number of measures will grow very large and could very fast create a nightmare when it comes to maintaining your model.
Calculation groups by example
Let’s go through an example of creating time intelligence logic using calculation groups and applying it to base measures. First, I’ll create the following measure Sales amount that returns the sales amount by multiplying Net price and Quantity for each row in table Sales
Sales Amount =
CALCULATE (
SUMX (
Sales,
Sales[Net Price] * Sales[Quantity]
)
)
Next step is to create a new calculation group through the Model Pane. I’ll call the calculation group Time Intelligence
You need to set discourage implicit measures to true in order to be able to create Calculation groups in Power BI Desktop
After creating the calculation group, the tree view shows one (1) newly created calculation group, one calculation group column and one calculation item.
After creation, the Calculation group is visible as a table in the semantic model. The Calculation group column is the column which stores the list of calculation items that you will be able to use in your model or report.
Let’s create two calculation items. YTD (Year to date) and PY (Prior year).
These two individual calculation items contain the logic on how to calculate YTD and PY. By selecting one of the calculation items you will see a formula bar at the top of the model view showing the DAX for the calculation item, similar as how you edit a measure.
By selecting a calculation item, you can change the logic of the calculation item by changing the DAX code in the formula editor. By default, the calculation item only contains the DAX function SELECTEDMEASURE(). This DAX function essentially references the measure where you use the calculation item in the current context.
Let’s add some DAX code to the two calculation items.
YTD =
CALCULATE (
SELECTEDMEASURE(),
DATESYTD ( 'Date'[Date] )
)PY =
CALCULATE (
SELECTEDMEASURE(),
CALCULATETABLE (
DATEADD ( 'Date'[Date], -1, YEAR )
)
)
The calculation group is now available in the table view in Power BI Desktop. Where you can see the Calculation group table and the two calculation items created.
The Ordinal column holds values for ordering the different calculation group items. At the time of this writing, it seems you can’t change this value directly in Power BI Desktop. However, you can change it using an external tool like Tabular Editor.
Now when we have the two calculation groups ready, let’s take a look at two visuals.
To the left is a table visual showing the two calculation items. These are simply values from a table, thus usable in visuals. They can also serve in measures and filters, allowing you to select the desired calculation item.
To the right is a simple matrix visual containing Years as Rows and a measure Sales as Values.
Now let’s take a look at how we can utilize the calculation groupin combination with this matrix visual. Let’s add the Calculation group column in the Columns section of the visual.
Voilà, the sales measure are now returning numbers for Sales PY and Sales YTD. Even though we are only using one single measure. By using SELECTEDMEASURE() function in the calculation items. The calculation items know that we want to use the Sales measure as a base for these calculations.
You can also apply calculation items as filters to your existing measures. For example, creating YTD calculations on top of existing measures such as Sales, Cost and Profit is possible if you do not want to duplicate the logic for YTD calculations across multiple new measures. SQLBI has an excellent article about this which also goes into depth around the complexity around calculation groups in this scenario. It’s important to fully understand the behavior before you apply this to your model in order to guarantee correct result of your measures.
This is only an example of one use case for calculation groups. You can use calculation groups in many other scenarios like creating dynamic format strings, switching measures in visuals or dynamically switching active relationships between tables and many more.
As you can see, using calculation groups opens up a lot of possibilities in your semantic model. But they do also come with some added complexity and it is important for you to understand how they work in order to use them correctly in your model.
Word of caution
It’s easy to get carried away using calculation groups in Power BI. Using them can greatly improve your model not only in maintainability but also by being able to add new functionalities to your model and report. However, it’s important to note that using calculation groups adds significant complexity to your model. There are things to be cautious about.
One aspect, discussed in the previous section, involves using them as filters in measures. Without fully understanding the best practices or the details of applying calculation groups, you may encounter incorrect numbers in your measures. Another example is using multiple calculation groups in a model which requires you to define a precedence for each calculation group so that the model knows the order of evaluation. This article on SQLBI thoroughly goes through a great example on how setting wrong precedence can cause your DAX to return wrong results.
Measures
The measures object in the semantic model contains all the measures in your model in one place. The measure is also available in the table where it is defined. By selecting a measure, the properties pane opens up where you can change properties of that single measure.
Even though the measures are displayed twice (once in the measure object and once in the actual table it is defined) they are not duplicated. It’s the same object just represented in two different places.
Relationships
The relationship object in the semantic model contains all relationships within your model in a single list. This provides you with a great overview of all relationships as well as the table included in the relationship as well as the direction of the relationship.
By selecting the relationships object or individual relationships, the properties pane allows you to edit properties on the top level (Relationships) or for each individual relationship. The great aspect of this property pane is that it enables completely offline authoring. This eliminates the need for underlying queries for validation or data preview, making the experience much smoother than traditionally editing the relationships using the Manage Relationships window.
Creating new relationships
By right clicking the Relationships object in the semantic model, you can also create new relationships. This presents a fast way of creating relationships in offline mode, providing a nice authoring experience. If you have experience from using Tabular Editor, you might see some similarities in the authoring experience, even though the user interface is different.
Roles
The Roles object in your semantic model contains a list of all security roles in your model. By right clicking the Roles object, you can navigate to the “Manage Roles” window where you can define your RLS (Row Level Security) roles. Currently, there is no way of defining OLS (Object Security Roles) in Power BI Desktop. However, the OLS roles will show up in the list of roles under your semantic model.
Tables
The Tables object in the semantic model provides a full list of all tables included in your semantic model. As you can see in the image below it also includes any existing calculation groups.
By right clicking on the Tables object, you can do things like creating new calculated tables, navigate to transform data window (Power Query) and open the Get Data window to connect to new data sources.
By expanding a table in the tree view, you can see four different types of items. Columns, Hierarchies, measures and partitions. The columns and measure objects are self explanatory. They contain the columns and measures of that table. The Hierarchies object holds all hierarchies created in the table. A hierarchy is a pre defined hierarchy level that consists of multiple columns of your choice. You can then reuse these hierarchies in visuals, for instance, in drill down scenarios.
Power BI Desktop introduced Partitions as a semantic model object in the preview of the new model explorer in October 2023. Before that it was possible to author partitions through XMLA endpoint using external tools. By expanding the Partitions object you can see all partitions of a particular table.
By partitioning a table in a Power BI semantic model, you can break up large table into smaller more manageable sets of data. Partitions can also be used to tune performance of large tables in a semantic model. It is also a key functionality when creating incremental refresh.
At the time of writing this article (October 2023), partitions can only be viewed in Power BI Desktop but not authored.
To create and manage partitions in a Power BI Dataset, you can use external tools such as Tabular Editor.
You can visit Microsoft learn for more information about managing partitions in tabular models.
Summary
In conclusion, the Model Pane in Power BI Desktop gives you a great overview for managing and understanding your semantic model. Through its structured view and various functionalities, it provides an intuitive interface for navigating and working with objects in your semantic model, enhancing the overall authoring experience.
This article dived into the details of the Model Pane, touching on different model objects like Calculation Groups, Measures, Relationships, Roles, Tables, Columns, Hierarchies and Partitions. The article also touched on the importance of understanding the complexity that comes with Calculation Groups to ensure accurate results from your measures. Through examples, the article demonstrates how to create and use Calculation Groups, and shows how to utilize them in visuals for more insightful data analysis.
Thank you for reading
If you enjoyed this article or learned something from it, please share it with others or comment below.
Subscribe to the right to get notified when I publish more content.