0
0
0
dark
0
0
0
Hand-Picked Top-Read Stories
Trending Tags
9 minute read
No comments

CONFIGURING SEMANTIC MODEL IN POWER BI

Alicia Foreman
May 31, 2026
Total
0
Shares
0
0
0

INTRODUCTION

Configuring a Power BI semantic model involves refining data structures, creating relationships, and setting up calculations. Semantic model is the last stop in the data pipeline before reports and dashboards are built. It is the end product of the raw data that has been extracted, transformed, loaded, modeled, built relationship, and written calculation. The Semantic model consist of Data connections to one or more data sources,Transformations that clean and prepare the data for reporting, Defined calculations and metrics based on business rules to ensure consistent reports and Defined relationships between tables.

Key words to note in Semantic Modelling are;

1. Fact table and Dimension table:
The Fact table records the quantitative and numerical data. It is where every single details are recorded. The Dimension table act as the descriptive companion to the fact table, containing the attributes or characteristics that provide context to the data.

2. Primary and Foreign Key:
Primary Keys are unique identifier assigned to a specific record with a database table ensuring that no two rows are identical or repeated. foreign Keys are columns or group of columns in one table that provides a link between data in two tables by referencing the primary key of another.

3. Star Schema
Star Schema is a data modeling technique where a central fact table is surrounded by several dimension tables that provide descriptive content.

4. Cardinality
Cardinality defines the kind of relationship between two tables. They are;

  • One to Many (1.*)
  • Many to one (*.1)
  • One to One (1.1)
  • Many to Many (.)
    The cardinality of a relationship is described by the “one” (1) or “many” (*) icons located at the ends of the relationship line.

5. Cross Filter Direction
The direction determine how filters propagate. Possible cross filter options are dependent on the relationship cardinality type.

  • One to Many – Single or Both sides
  • One to One – Both sides
  • Many to Many – Single to either table or both
    The cross filter direction of a relationship is described by the arrows located in the middle of the relationship line.

6. Active and Inactive Relationship
An Active relationship is the primary relationship that is used by Power BI to calculate and filter data automatically.
An inactive relationship is a secondary relationship activated with the Power BI DAX function temporarily whenever there is a need to perform a specific calculation.
An active relationship is a solid line; an inactive relationship is a dotted line.

7. Disconnected Table
Disconnected Table is a table that doesn’t need a relationship to another model table and are useful in what-if scenario or field parameters.

ARCHITECTURE OVERVIEW

In this blog, you learn how to:

  • Create model relationships.
  • Configure table and column properties.
  • Create hierarchies.
  • Create quick measures.
  • Configure a many-to-many relationship

A. Create Model Relationships
Data modeling is the process of organizing data into tables and creating relationships. The data model is a visual and structural blueprint that defines how data is stored, organized and related within a database.

STEPS

  1. In Power BI Desktop, to view all table fields, in the Data pane, right-click an empty area, and then select Expand All.
    Image 1
  2. To create a table visual, in the Data pane, from inside the Product table, check the Category field.
    Image 2
  3. To add another column to the table, in the Data pane, check the Sales | Sales field.
    Image 3
  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. This is because there isn’t a model relationship between these tables.
    Image 4
  5. To switch to the model designer, at the left select the Model view icon.
    Image 5
  6. On the Home ribbon, select Manage Relationships.
    Image 6
  7. In the Manage Relationships window, notice that no relationships are yet defined.
    Image 7
  8. To create a relationship, select + New relationship.
    Image 8
  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.
    Image 9
  10. The following properties were automatically configured:
  • ProductKey columns in each table are selected.
  • Cardinality type is One To Many (1:*), which was automatically detected.
  • Cross Filter Direction type is Single.
  • Make This Relationship Active is checked.
    Image 10
    11.Select Save, notice in the Manage Relationships window that the new relationship is listed, and then select Close.
    Image 11
    12.In the model diagram, there’s now a connector between the two tables (between the Product and Sales table which is connected by the ProductKey column).
    Image 12
    13.Switch to Report view, and then notice that the table visual updated to display different values for each product category.
    Image 13

B. Create additional relationships
New relationships in Power BI are also created through the drag and drop columns.

STEPS

  1. Firstly, switch to Model view.
  2. From the Reseller table, drag the ResellerKey column onto the ResellerKey column of the Sales table.
    Image 2
  3. In the New relationship window, review the configuration, and then select Save.
    Image 3
  4. Additional model relationships are created using the following;
    Region table to Sales table using SalesTerritoryKey columns as the common column for both tables. Salesperson table to Sales table, using the EmployeeKey columns as common key for both tables.
  5. In the model view diagram, tables are arranged so that the Sales table is positioned in the center of the diagram, and the related tables are arranged about it. (Disconnected tables are positioned to the side).
    Image 5
  6. Save the Power BI Desktop file.

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

STEPS

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

D. Configure the Region table
This task configures the Region table with a hierarchy and updated categories.

STEPS

  1. In the Region table, create a hierarchy named Regions, with the three levels: Group, Country and Region.
    Image 1
  2. Select the Country column (not the Country hierarchy level).
    Image 2
  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.
    Image 3

E. Configure the Reseller table
This task configures the Reseller table to add a hierarchy and update data categories.

STEPS

  1. In the Reseller table, create a hierarchy named Resellers, with the two levels, Business Type and Reseller
    Image 1
  2. Create a second hierarchy named Geography, with the following four
    levels: Country-Region, State-Province, City, and Reseller
    Image 2
  3. Set the data category for the following columns (not within the hierarchy):

  • Country-Region to Country/Region
    Image 3a

  • State-Province to State or Province
    Image 3b

  • City to City
    Image 3c

F. Configure the Sales table
This task configure the Sales table with updated descriptions, formatting, and summarization.

STEPS

  1. In the Sales table, select the Cost column.
    Image 1
  2. In the Properties pane, in the Description box, enter: Based on standard cost
    Image 2
  3. Select the Quantity column.
    Image 3
  4. In the Properties pane, from inside the Formatting section, set the Thousands Separator property to Yes.
    Image 4
  5. Select the Unit Price column.
    Image 5
  6. In the Properties pane, from inside the Formatting section, set the Decimal Places property to 2.
    Image 6
  7. In the Advanced group (you may need to scroll down to locate it), in the Summarize By dropdown list, select Average.
    Image 7
    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.

G. Bulk update properties
This task updates multiple columns using single bulk updates. This approach is used to hide columns, and format column values.

STEPS

  1. In the Data pane (or model diagram), select the ProductKey column in the Product table
    Image 1.
  2. While pressing the Ctrl key, select the following 13 columns SalesTerritoryKey (Region Table), ResellerKey (Reseller), EmployeeKey, ProductKey, ResellerKey, SalesOrderNumber and SalesTerritoryKey (Sales Table), EmployeeID, EmployeeKey, UPN (Salesperson Table), EmployeeKey, SalesTerritoryKey (SalespersonRegion), and EmployeeID (Targets).
    Image 2

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

  4. Multi-select the following three columns: Standard Cost (Product Table), Cost and Sales (Sales Table).

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

H. Explore the model interface

STEPS

This task explains how to switch to Report view, review the data model interface, and configure the auto date/time setting.

  1. Switch to Report view.
    Image 1
  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 Sales (Cost field).
  • Expand the Sales (OrderDate field), which reveals a Date Hierarchy.
    Image 3
    They were created automatically as a default setting which commences on January 1 of each year which is not in syn with the dataset used in this analysis, whose financial year commenced on July 1.
  • To turn off the auto date/time setting, navigate to File Image 4
    Open the Options and Settings and Options.
    Image 4b
  1. In the Options window, on the Current File section, navigate to Data Load > Time Intelligence, and uncheck Auto Date/Time.
    Image 5
  2. In the Data pane, notice that the date hierarchies are no longer available.
    Image 6

I. Create quick measures

STEPS

This task creates 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.
    Image 1
  2. In the Quick Measure pane, in the Select a Calculation dropdown list.
    Image 2a
    From inside the Mathematical Operations group, select Subtraction.
    Image 2b
  3. From the Data pane, drag the Sales (Sales field) into the Base Value well.
    Image 3
  4. Drag the Sales (Cost field) into the Value to Subtract box.
    Image 4
  5. Select Add.
    Image 5
  6. In the Data pane, inside the Sales table, notice that new measure. Measures are indicated by the calculator icon.
    Image 6
  7. To rename the measure, right-click it, select Rename, then rename as Profit.
    Image 7
    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:
    Image 8
  9. Use the Division mathematical operation.
    Image 8a
  10. Set the Numerator to the Sales (Profit field).
    Image 8b
  11. Set the Denominator to Sales (Sales field).
    Image 8c
  12. Rename the measure as Profit Margin.
    Image 8d
  13. Ensure the Profit Margin measure is selected, and then on the Measure Tools contextual ribbon, set the format to Percentage, with two decimal places.
    Image 9
  14. To test the two measures, first select the existing table visual on the page.
    Image 10
  15. In the Data pane, check the Profit and Profit Margin measures to add them to the table.
    Image 11
  16. Select and drag the right guide to widen the table visual.
    Image 12
  17. Verify that the measures produce reasonable results that are correctly formatted.
    Image 13

J. Create a many-to-many relationship

STEPS

This task creates 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.
    Image 1
  2. To create a new table visual, in the Data pane, check the following two fields: Salesperson (Salesperson) and Sales (Sales).
    Image 2
  3. Notice that Michael Blythe has generated almost 9 million dollars of sales.
    Image 3
  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:
  6. Salesperson | EmployeeKey to SalespersonRegion | EmployeeKey
  7. Region | SalesTerritoryKey to SalespersonRegion | SalesTerritoryKey
    The SalespersonRegion table can be considered to be a bridging table.
  8. Switch to Report view, and then notice that the visual hasn’t updated the sales result for Michael Blythe hasn’t changed.
    Image 6
  9. Switch back to Model view, and then follow the relationship filter directions (arrowhead) from the Salesperson table.
    Note 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).
    Image 7
  10. To edit the relationship between the Region and SalespersonRegion tables, double-click the relationship.
    Image 8
  11. In the Edit Relationship window, in the Cross Filter Direction dropdown list, select Both.
    Image 9
    10.Check the Apply Security Filter in Both Directions checkbox.
    Image 10
    11.Select Save.
    Image 11
    12.Notice that the relationship has a double arrowhead now.
    Image 12
    13.Switch to Report view, and then notice that the sales values have still not changed.
    Image 13
    14.Switch to Model view.
  12. To force filter propagation via the bridging table, edit (double-click) the relationship between the Salesperson and Sales tables.
    Image 15
    16.In the Edit Relationship window, uncheck the Make This Relationship Active checkbox.
    17.Select Save.
    Image 17
    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.
    Image 18
    19.Switch to Report view, and then notice that the sales for Michael Blythe are now nearly 22 million dollars.
    Image 19
    20.Notice also, that the sales for each salesperson—if added—would exceed the table total.
    21.Switch to Model view, and then in the model diagram, select
    the Salesperson table.
    Image 21
    22.In the Properties pane, in the Name box, replace the text with Salesperson (Performance).
    Image 22
    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.

K. Relate the Targets table

STEPS

This task creates a relationship to the Targets table.

  1. Create a relationship from the Salesperson (Performance) (EmployeeID column) and the Targets (EmployeeID column).
    Image 1
  2. In Report view, add the Targets (Target field) to the table visual.
    Image 2
  3. Resize the table visual so all columns are visible.
    Image 3
Total
0
Shares
Share 0
Tweet 0
Pin it 0
Leave a Reply

Your email address will not be published. Required fields are marked *

Previous Post

Streaming an LLM response, in 4 GIFs

May 31, 2026
Related Posts