Configure a semantic model in Power BI

Introduction

In the world of data analytics, a report is only as strong as the engine driving it. Configuring a Semantic Model in Power BI is the critical step where raw data transforms into a structured, business-friendly layer that users can actually trust.

When your model comprises more than one table, you need to ensure that appropriate relationships exist between the tables. Relationships propagate filters applied on one model table to a different model table. They continue to propagate so long as there's a relationship path to follow, which can involve propagation to multiple tables.

Whether you're looking to optimize performance, simplify complex DAX measures, or ensure a "single source of truth" for your organization, mastering the semantic layer is what separates a basic dashboard from a high-performance analytical tool.

In this post, we’ll dive into the essential configurations you need to turn your data into actionable insights.

Create model relationships
The file was configured to not identify relationships between tables, which isn't the default setting, but is recommended to prevent extra work creating the correct relationships for your model.

1. In Power BI Desktop, to view all table fields, in the Data pane, right-click an empty area, and then select Expand All.
2. To create a table visual, in the Data pane, from inside the Product table, check the Category field.
3. To add another column to the table, in the Data pane, scroll down and check the Sales field inside the Sales table.
4. Notice that the table visual lists four product categories, and that the sales value is the same for each, and the same for the total. The issue is that the table is based on fields from different tables. The expectation is that each product category displays the sales for that category. However, because there isn't a model relationship between these tables, the Sales table isn't filtered. You'll now add a relationship to propagate filters between the tables.
5. To switch to the model designer, at the left select the Model view icon.
6. On the Home ribbon, select Manage Relationships.
7. In the Manage Relationships window, notice that no relationships are yet defined.
8. To create a relationship, select + New relationship.
9. To configure a relationship from Product table to Sales table, in the From table dropdown list, select the Product table, and in the To table dropdown list, select the Sales table.
10. Notice the following properties were automatically configured:
11. ProductKey columns in each table are selected. The columns were selected because they share the same name and data type. You may need to find matching columns with different names in real data.
12. Cardinality type is One To Many (1:*). The cardinality was automatically detected, because Power BI understands that the ProductKey column from the Product table contains unique values. One-to-many relationships are the most common cardinality.
13. Cross Filter Direction type is Single. Single filter direction means that filters propagate from the "one side" to the "many side". In this case, it means filters applied to the Product table will propagate to the Sales table, but not in the opposite direction.
14. Make This Relationship Active is checked. Active relationships propagate filters. It's possible to mark a relationship as inactive so filters don't propagate. Inactive relationships can exist when there are multiple relationship paths between tables. In this case, model calculations can use special functions to activate them.
15. Select Save, notice in the Manage Relationships window that the new relationship is listed, and then select Close.
16. In the model diagram, notice there's now a connector between the two tables (you might choose to reposition the tables to see the relationship more clearly). You can interpret many of the relationship properties by looking at the relationship line in the diagram: Cardinality is represented by the 1 and (*) indicators. Filter direction is represented by the arrow head. A solid line represents an active relationship; a dotted line represents an inactive relationship.

Tip: If you hover the cursor over the relationship to highlight the related columns.
13.Switch to Report view, and then notice that the table visual updated to display different values for each product category.
Filters applied to the Product table now propagate to the Sales table.

Create additional relationships
There's an easier way to create a relationship. In the model diagram, you can drag and drop columns to create a new relationship.

1. To create a new relationship using a different technique, switch to Model view.
2. From the Reseller table, drag the ResellerKey column onto the ResellerKey column of the Sales table. Important: Sometimes a column doesn't want to be dragged. If this situation arises, select a different column, and then select the column you intend to drag again, and then try again. Ensure that you see the new relationship added to the diagram.
3. In the New relationship window, review the configuration, and then select Save.
4. Use the new technique to create the following two model relationships:

• SalesTerritoryKey column from the Region table to SalesTerritoryKey column from the Sales table.
• EmployeeKey column from the Salesperson table to EmployeeKey colmun from the Sales table.

5.In the diagram, arrange the tables so that the Sales table is positioned in the center of the diagram, and the related tables are arranged about it. Position the disconnected tables to the side.

6.Save the Power BI Desktop file.

Configure the Product table
In this task, you'll configure the Product table with a hierarchy and display folder.

1. Switch to Model view.
2. In the Data pane, if necessary, expand the Product table to reveal all fields.
3. In the Product table, right-click the Category column, and the select Create hierarchy.
4. In the Properties pane, in the Name box, replace the value with Products.
5. To add levels to the hierarchy, in the Hierarchy dropdown list, select Subcategory and then select Product.
6. In the Data pane, notice the Products hierarchy. To reveal the hierarchy levels, expand it.
7. To organize columns into a display folder, in the Data pane, first select the Background Color Format column.
8. While pressing the Ctrl key, select the Font Color Format column.
9. In the Properties pane, in the Display Folder box, enter Formatting.
10. In the Data pane, notice that the two columns are now inside a folder. Display folders are a great way to organize tables, especially for tables that comprise many fields. They're logical presentation only.

Configure the Region table
In this task, you'll configure the Region table with a hierarchy and updated categories.

1. In the Region table, create a hierarchy named Regions, with the following three levels:

• Group
• Country
• Region
• SalesTerritoryKey

2.Select the Country column (not the Country hierarchy level).
3.In the Properties pane, expand the Advanced section (located at the bottom of the pane), and then in the Data Category dropdown list, select Country/Region.

Data categorization can provide hints to the report designer. In this case, categorizing the column as country or region provides more accurate information to Power BI when it renders a map visualization.

Configure the Reseller table
In this task, I'll configure the Reseller table to add a hierarchy and update data categories.

1. In the Reseller table, create a hierarchy named Resellers, with the following two levels:

• Business Type
• Reseller

2.Create a second hierarchy named Geography, with the following four levels:

• Country-Region
• State-Province
• City
• Reseller

3.Set the data category for the following columns (not within the hierarchy):

• Country-Region to Country/Region
• State-Province to State or Province
• City to City

Configure the Sales table
In this task, you'll configure the Sales table with updated descriptions, formatting, and summarization.

1. In the Sales table, select the Cost column.
2. In the Properties pane, in the Description box, enter: Based on standard cost.

Descriptions can be applied to tables, columns, hierarchies, or measures. In the Data pane, description text is revealed in a tooltip when a report author hovers their cursor over the field.

3.Select the Quantity column.
4.In the Properties pane, from inside the Formatting section, set the Thousands Separator property to Yes.

5.Select the Unit Price column.
6.In the Properties pane, from inside the Formatting section, set the Decimal Places property to 2.
7.In the Advanced group (you may need to scroll down to locate it), in the Summarize By dropdown list, select Average.

By default, numeric columns will summarize by summing values together. This default behavior isn't suitable for a column like Unit Price, which represents a rate. Setting the default summarization to average will produce a meaningful result.

Bulk update properties
In this task, you'll update multiple columns using single bulk updates. You'll use this approach to hide columns, and format column values.

1. In the Data pane (or model diagram), select the ProductKey column for the Product table.
2. While pressing the Ctrl key, select the following 13 columns (spanning multiple tables):

• SalesTerritoryKey column from Region table
• ResellerKey column from Reseller table
• EmployeeKey column from Sales table
• ProductKey column from Sales table
• ResellerKey column from Sales table
• SalesOrderNumber column from Sales table
• SalesTerritoryKey column from Sales table
• EmployeeID column from Salesperson table
• EmployeeKey column from Salesperson table
• UPN column from Salesperson table
• EmployeeKey column from SalespersonRegion table
• SalesTerritoryKey column from SalespersonRegion table
• EmployeeID column from Targets table

3.In the Properties pane, set the Is Hidden property to Yes.
The columns were hidden because they're either used by relationships or will be used in row-level security configuration or calculation logic.

The SalesOrderNumber column will be used in a calculation in Create DAX Calculations in Power BI Desktop in a another blog post.
4.Multi-select the following three columns:

• Standard Cost column from Product table
• Cost column from Sales table
• Sales column from Sales table

5.In the Properties pane, from inside the Formatting section, set the Decimal Places property to 0 (zero).

Explore the model interface
In this task you'll switch to Report view, review the data model interface, and configure the auto date/time setting.

1. Switch to Report view.
2. In the Data pane, notice the following:

• Columns, hierarchies and their levels are fields, which can be used to configure report visuals.
• Only fields relevant to report authoring are visible.
• The SalespersonRegion table isn't visible—because all of its fields are hidden.
• Spatial fields in the Region and Reseller table are adorned with a spatial icon.
• Fields adorned with the sigma symbol (Ʃ) will summarize, by default.
• A tooltip appears when hovering the cursor over the Cost field of the Sales table.

3.Expand the OrderDate field of the Sales table, and then notice that it reveals a Date Hierarchy. The TargetMonth field of the Targets Table delivers a similar hierarchy.

Important: These hierarchies weren't created by you. They were created automatically as a default setting. There's a problem, however. The Adventure Works financial year commences on July 1 of each year. But, in these automatically created date hierarchies, the date hierarchy year commences on January 1 of each year.

4.To turn off the auto date/time setting, navigate to File > Options and Settings > Options.
5.In the Options window, on the Current File section, navigate to Data Load > Time Intelligence, and uncheck Auto Date/Time.

6.In the Data pane, notice that the date hierarchies are no longer available.

Create quick measures
In this task, you'll create two quick measures to calculate profit and profit margin. A quick measure creates the calculation formula for you. They're easy and fast to create for simple and common calculations.

1. In the** Data** pane, right-click the Sales table, and then select New Quick Measure.

2.In the Quick Measure pane, in the Select a Calculation dropdown list, from inside the Mathematical Operations group, select Subtraction.

3.From the Data pane, drag the Sales field of the Sales table into the Base Value well.
4.Drag the Cost field of the Sales table into the Value to Subtract box.
5.Select Add.

6.In the Data pane, inside the Sales table, notice that new measure.

Measures are indicated by the calculator icon.

7.To rename the measure, right-click it, select Rename, then rename as Profit.

Tip: To rename a field, you can also double-click it, or select it and press F2.

8.In the Sales table, add a second quick measure, based on the following requirements:

Important: If the option to create a quick measure doesn't appear in the context menu, use the command located on the Home ribbon, from inside the Calculations group.

• Use the Division mathematical operation.
• Set the Numerator to the Sales | Profit field.
• Set the Denominator to Sales | Sales field.
• Rename the measure as Profit Margin.

9.Ensure the Profit Margin measure is selected. Then on the Measure Tools contextual ribbon, set the format to Percentage, with two decimal places.

10.To test the two measures, first select the existing table visual on the page.
11.In the Data pane, check the Profit and Profit Margin measures to add them to the table.

12.Verify that the measures produce reasonable results that are correctly formatted.

Create a many-to-many relationship
In this task, you'll create a many-to-many relationship between the Salesperson table and the Sales table.

1. In Report view, select a blank area of the report page.
2. To create a new table visual, in the Data pane, check the following two fields:

• Salesperson column of Salesperson table
• Sales column of Sales table

_The table visual displays sales made by each salesperson. However, there's another relationship between salespeople and sales. Some salespeople belong to one, two, or possibly more sales regions. In addition, sales regions can have multiple salespeople assigned to them.

From a performance management perspective, a salesperson's sales (based on their assigned territories) need to be analyzed and compared with sales targets. You'll create relationships to support this analysis in the next exercise._

3.Notice that Michael Blythe has generated almost 9 million dollars of sales.
4.Switch to Model view, then drag the SalespersonRegion table to position it between the Region and Salesperson tables.
5.Use the drag-and-drop technique to create the following two model relationships:

• EmployeeKey of Salesperson table to EmployeeKey of SalespersonRegion table.
• SalesTerritoryKey of Region table to SalesTerritoryKey of SalespersonRegion table.

The SalespersonRegion table can be considered to be a bridging table.

6.Switch to Report view, and then notice that the visual hasn't updated—the sales result for Michael Blythe hasn't changed.

7.Switch back to Model view, and then follow the relationship filter directions (arrowhead) from the Salesperson table.

Consider that the Salesperson table filters the Sales table. It also filters the SalespersonRegion table, but it doesn't continue by propagating filters to the Region table (the arrowhead is pointing the wrong direction).

8.To edit the relationship between the Region and SalespersonRegion tables, double-click the relationship.
9.In the Edit Relationship window, in the Cross Filter Direction dropdown list, select Both.
10.Check the Apply Security Filter in Both Directions checkbox.
11.Select Save.

12.Notice that the relationship has a double arrowhead now.

13.Switch to Report view, and then notice that the sales values have still not changed.

The issue now relates to the fact that there are two possible filter propagation paths between the Salesperson and Sales tables. This ambiguity is internally resolved, based on a "least number of tables" assessment. To be clear, you shouldn't design models with this type of ambiguity—the issue will be addressed in part later in this post, and by the completion of the Create DAX Calculations in Power BI Desktop in another post.

14.Switch to Model view.
15.To force filter propagation via the bridging table, edit (double-click) the relationship between the Salesperson and Sales tables.
16.In the Edit Relationship window, uncheck the Make This Relationship Active checkbox.

17.Select Save.
Filter propagation will now follow the only active path.

18.In the model diagram, notice that the inactive relationship is represented by a dotted line.

19.Switch to Report view, and then notice that the sales for Michael Blythe are now nearly 22 million dollars.

20.Notice also, that the sales for each salesperson—if added—would exceed the table total.

It's a common observation of a many-to-many relationship due to the double, triple, etc. counting of regional sales results. Consider Brian Welcker, the second salesperson listed. His sales amount equals the total sales amount. It's the correct result due to the fact that he's the Director of Sales; his sales are measured by the sales of all regions.
While the many-to-many relationship is now working, it's now not possible to analyze sales made by a salesperson (because the relationship is inactive). You'll be able to reactivate the relationship when you introduce a calculated table that will allow analyzing sales made in the sales region(s) assigned to the salesperson (for performance analysis) in the Create DAX Calculations in Power BI Desktop lab.

21.Switch to Model view, and then in the model diagram, select the Salesperson table.
22.In the Properties pane, in the Name box, replace the text with Salesperson (Performance).

The renamed table now reflects its purpose: it's used to report and analyze the performance of salespeople based on the sales of their assigned sales regions.

Relate the Targets table
In this task, you'll create a relationship to the Targets table.

1. Create a relationship from the EmployeeID column of Salesperson (Performance) table and the EmployeeID column of Targets table.

2.In Report view, add the Target field of Targets table to the table visual.

Resize the table visual so all columns are visible. You can use the Focus Mode. Click on anywhere within the table and it appears.

It's now possible to visualize sales and targets—but for now take care for two reasons. First, there's no filter on a time period, and so targets also include future target amounts. Second, targets aren't additive, and so the total shouldn't be displayed. They can either be disabled by formatting the visual or removed by using calculation logic.