Power BI

Active vs. Inactive Relationship in a Data Model

Our Power BI courses across Australia and Power BI training across Australia cover the data modeling principles in detail. We address all the issues from basic to advanced level. To assist other users, we have developed a blog series to address such issues. Here’s the link to the blog series: http://staging3.powerbitraining.com.au/category/data-modeling-issues-and-solutions/ In this blog post, a step-by-step guide on dealing with active and inactive relationships is presented but let’s summarize the concepts covered so far:

1. Importing Data

Importing data refers to procuring the data tables into Power BI desktop or service. This step involves creating a connection with the data base to access the data. For a step by step guide on this: http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

2. Data Preparation

Data Preparation involves ensuring that the data is capable of delivering value. In this phase, the raw data is manipulated to extract useful information from it. For a step by step guide on this: http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

3. Creating Date Table

Date Table is important for temporal analysis. The date table is created using the CALENDER() function or the CALENDERAUTO() function. Both of these functions have different input parameters and produce different results. For a step by step guide on this: http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/

4. Setting Model Design

The model design consists of creating relationship between the data tables procured for the task at hand. The tables are linked on the basis of primary and foreign keys. If these keys are absent then the table cannot be linked to another table.

For a step by step guide on this issue: http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/

5. Creating Surrogate Key

Surrogate Key is the unique identifier in any table. It is important to create a relationship between the two tables. This key acts as a primary key in one table and foreign key in the other.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/

6. Merging Queries

Merging queries involves combining two data tables based on a common column. This column should have similar values in the both tables.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-6-power-bi-training-in-sydney-power-bi-training-in-melbourne/

7. Creating Calculated Columns

Calculated Columns are created to provide you with insights related to your data set and make data visualization easy and meaningful.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/

8. Flattening the Product Table

Flattening of the table refers to linking the tables attached to the ends of the schema. In data terminology it means changing the snow flake to star schema because star schema is the most preferred data model by industry experts. Modeling.

Flattening dimensions in Power BI training

Flattening of Product table is important due to two reasons. Firstly, it helps to organizes the data model and secondly, hierarchy can be created in the same table of the data model.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-8-power-bi-training-in-sydney-power-bi-training-in-melbourne/

 9. Creating Product Hierarchy

Power BI allows you to create Product Hierarchy for columns present in the same table.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-9-power-bi-training-in-sydney-power-bi-training-in-melbourne/

10. Active vs. Inactive Relationship

The Sales table contains two date fields, Order Date and Ship Date. We have already configured Order Date with the Date Table. Let’s see how you can use the second Date field.

To use Ship Date:

1. Click on Ship Date in the Sales Table and drag the cursor to date.

A dotted line is created between the Sales and Date table. This shows an inactive relationship between these two.

Bidirectional relationship in Power BI course

2. Click on the dotted line.

Edit Relationship box appears.

3. From the Cardinality drop-down menu, click on Many to one.

4. From the Cross filter drop-down menu, click on Single.

Two Date table in Power BI training

5. Click OK.

Active vs. Inactive relationship in Power BI training

The date table must always have a cardinality of one to many with one the date side and many in the fact table.

Now we add description so that it is clear that the date table filters on Order Date

6. Click on Model Tab in the Left Ribbon.

7. Click on the Properties Pan.

8. In the Description area, type Filter Date by Order Date.

9. Click on Report tab in the left Ribbon to navigate to the Report Canvas.

10. Click on the Sales Table in the Field. Click on New Measure.

11. Type the following expression in the Formula Bar.

Using this DAX expression we will use the inactive Date Relationship.

DAX for using Date in Power BI training

12. Click on Table Visual in the Visualization Pane.

13. Drag and drop the Date field and the Name of Month from the Date Table to the Values.

14. Drag and drop the Sales Amount column and the Sale Shipped column to the Values.

linking two dates in Power BI training

Using the DAX measure you can see that the Sales recorded on January 20th, 2019 was Shipped on January 24th, 2019.


Some Common issues in Data Modeling issue emerge when the data modeling concepts are put into practice. One of the common problem is having multiple date entries in a single table. The dimension date table is configured with one of the date column and the other column has an inactive relationship. In this blog post, these active and inactive relationships have been discussed.

Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.

More Checkouts for Power BI Training in Australia.

Back to list

Leave a Reply

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