Financial Analytics & Automation / Power BI Dashboard Creation Series
Introduction
In the previous article Vol. 3, we used Power Query to import five CSV files, set up headers, convert data types, and add custom columns for KPI calculations. This created the following five tables to be used in the dashboard:
Dim_line_name(Line Master)Fact_monthly_kpi(Sales and COGS data)Fact_defect_rate(Defect Rate data)Fact_equipment_utilization(Equipment Utilization data)Fact_inventory_turnover(Inventory Turnover data)
However, we have not yet created a relationship between the master tables and the fact tables we will create in this article. The relationships between the tables also have not yet been defined.
Without these relationships, we cannot take full advantage of Power BI’s analytical capabilities, such as analyzing data by production line or filtering the dashboard to a specific period.
In this article, we will complete two tasks:
- Create a Calendar Table
- Set up relationships between each table
Once these are configured, all six tables will be connected as a single data model, providing the foundation for the DAX calculations and dashboard development covered in the following articles.
What Is a Calendar Table?
In practice, there are many situations where you need to perform time-based analysis, such as:
- Calculating year-over-year (YoY) changes
- Aggregating data by quarter
- Analyzing multiple Fact tables over the same period
In Vol. 3, we converted the Year/Month columns in each Fact table to the Date data type. This was necessary so that they would have the same data type as the Date column in the Calendar table we are about to create, allowing us to establish relationships between them.
A Calendar table is a dedicated master table that allows Power BI to properly work with dates. Many time-based calculations, such as year-over-year comparisons, cumulative totals, and quarterly aggregations, are based on this table.
By connecting all Fact tables to the Calendar table, we can aggregate and analyze different types of data using the same time dimension. A Calendar table is fundamental to time-based analysis in Power BI and is commonly created in real-world projects.
Step 1: Create a Calendar table using DAX
This time, we will create a Calendar table using DAX in Power BI Desktop.
From the “Modeling” tab, click “New Table.”
Enter the following DAX formula in the formula bar.
Calendar =
ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2026,12,31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"YearMonth", FORMAT([Date], "YYYY-MM")
)The CALENDAR function generates a continuous list of dates, with one row for each day between the specified start and end dates. In practice, the date range is usually set based on the minimum and maximum dates in the Fact tables.
We then use “ADDCOLUMNS” to add the “Year,” “Month,” and “YearMonth” columns.
The “Year” column will be used later as a period slicer. We will not use the “Month” and “YearMonth” columns in this series, but they are commonly included when expanding a Calendar table for real-world use cases, such as quarterly analysis or displaying month names.
The date range of the Calendar table should include all dates found in the Fact tables. If the range is too short, some data may be excluded from the analysis. If it is unnecessarily long, periods with no actual data may appear in the report. Therefore, it is important to set the date range appropriately based on the data in your Fact tables.
After creating the Calendar table, make sure that the “Date” column has the “Date data” type. If necessary, change the “Format” to “Short Date”.
Step 2: Mark the Calendar Table as a Date Table
Simply creating the table does not tell Power BI that it is specifically a date table.
To ensure that date intelligence functions, such as SAMEPERIODLASTYEAR and DATEADD, work correctly, you need to explicitly mark the table as a date table.
With the newly created Calendar table selected, go to the “Table tools” tab and click “Mark as date table”.
In the dialog box that appears, select the “Date” column as the date column and click “OK”.
Once this setting is configured, the DAX calculations for year-over-year comparisons and other time-based analysis covered in Vol. 5 and later will work correctly.
This step is easy to overlook, so make sure to complete it.
Step 3: Organize the Table Structure (Star Schema)
Before setting up the relationships, let’s clarify the roles of each of the six tables.
| Table | Role |
|---|---|
| Calendar | Date Master |
| Dim_line_name | Master of the Line |
| Fact_monthly_kpi | Actual Sales and Cost Data |
| Fact_defect_rate | Historical Data on Defect Rates |
| Fact_Equipment_Utilization | Historical Data on Equipment Utilization Rates |
| Fact_Inventory_Turnover | Historical Data on Inventory Turnover Rate |
As explained in Vol. 3, “Dim” tables are master tables, while “Fact” tables contain actual data. The Calendar table is also a type of master table, specifically for dates.
In Power BI, master tables are placed at the center of the model and connected to the Fact tables. This structure is called a Star Schema and is one of the standard data modeling approaches recommended for Power BI.
Instead of connecting Fact tables directly to one another, the model is built around master tables. This creates a simpler and easier-to-maintain data model.
A well-designed star schema also helps maintain consistency in calculations and can improve Power BI performance.
The next step is to build relationships between these tables using the star schema in the Model View.
Step 4: Set Up Relationships in the Model View
Open the Model view by clicking the “Model view” icon on the left side of Power BI Desktop.
Power BI automatically detect relationships based on column names, data types, and the contents of the data.
In our sample model, Dim_line_name should already be automatically connected to each Fact table. However, depending on your environment, these relationships may not be created.
In the Model view, check that Dim_line_name and each Fact table are connected by solid lines, as shown in the screenshot.
If a relationship has not been created, manually connect the tables using the same procedure described below for the Calendar table. If an incorrect relationship has been created, “right-click the solid line”, select “Delete”, and then recreate the correct relationship using the appropriate columns.
The Calendar table, on the other hand, was newly created in this article, so Power BI will not automatically create relationships between it and the Fact tables. We therefore need to create these relationships manually.
Drag the “Date” column from the Calendar table and drop it onto the “Year/Month” column in the Fact_monthly_kpi table.
The “New relationship” dialog box will appear. Make sure the settings are as follows, then click “OK”. These settings are usually detected automatically, but correct them if necessary.
| Settings | Settings |
|---|---|
| Cardinality | One-to-many (1:*) |
| Cross-filter direction | Single |
| Mark this relationship as active | On |
Next, create the same type of relationship for the following three tables:
Calendar[Date] →Fact_defect_rate[Year/Month]Calendar[Date] →Fact_equipment_utilization[Year/Month]Calendar[Date] →Fact_inventory_turnover[Year/Month]
Once all the relationships have been created, a star schema centered around “Calendar” and “Dim_line_name” is complete.
Step 5: Save the file
Once all the settings are complete, press “Ctrl + S” to save the file.
The KPIDashboard.pbix file created in Vol. 3 will now include the Calendar table and all the relationships we have added.
Frequently Asked Questions (FAQ)
1. What is the difference between a Dimension table and a Fact table?
A Dimension table manages data that provides the “dimensions” or “axes” for analysis, such as dates, products, customers, and line names.
A Fact table, on the other hand, stores numerical or transactional data, such as sales amounts, defect rates, and equipment utilization rates.
In this dashboard, Calendar and Dim_line_name are Dimension tables, while the four Fact tables contain the actual KPI data.
In Power BI, the basic approach is to connect Fact tables to Dimension tables in a Star Schema.
2. What is DAX?
DAX (Data Analysis Expressions) is a formula language used in Power BI to create calculated columns and measures.
The syntax is similar to Excel formulas, but DAX can perform more advanced calculations required for dashboards, including aggregations across multiple tables, year-over-year comparisons, and cumulative totals.
In Vol. 5, we will cover the basic concepts of DAX and walk through how to create measures for calculating KPIs.
3. What is Relationship Cardinality?
Cardinality defines how two tables are related to each other.
Power BI supports four types of cardinality:
One to One (1:1)
Used when values are unique in both tables.
For example, an employee master table and an employee details table may have a one-to-one relationship when each employee has exactly one corresponding detail record.
One to Many (1:*)
This is the most commonly used type of relationship.
Values are unique in the master table but can appear multiple times in the Fact table.
In our dashboard, the relationships between Calendar or Dim_line_name and the four Fact tables are examples of one-to-many relationships.
Many to One (*:1)
This is the same relationship as One to Many, viewed from the opposite side of the relationship.
Depending on the order in which the tables are connected, Power BI may display the relationship as One to Many or Many to One. The underlying relationship is the same.
Many to Many (:)
Used when values can appear multiple times in both tables.
Many-to-many relationships can be useful in complex data models, but they can also lead to unexpected aggregation results. For beginners, it is generally best to avoid them whenever possible.
In this series, all relationships use One to Many (1:*). After creating each relationship, make sure that Power BI shows the expected One-to-Many relationship.
4. What is Cross-filter Direction?
Cross-filter direction determines how filters propagate between tables through a relationship.
In this series, we use the Single direction, where filters flow from the Calendar and Dim_line_name tables to the Fact tables.
For a simple star schema, a single filter direction is generally recommended because it makes the data model easier to understand and reduces the risk of unexpected calculation results.
5. What is the Difference Between Active and Inactive Relationships?
An active relationship is a relationship that Power BI uses automatically for normal calculations and analysis.
An inactive relationship exists in the data model but is not used automatically.
Normally, only one active relationship can exist between a given pair of tables. When a table contains multiple date columns, such as Order Date and Shipment Date, you may need to create inactive relationships and use the DAX USERELATIONSHIP function to activate a specific relationship when needed.
For the dashboard in this series, all relationships will be active, so there is no need to create inactive relationships.
Summary
In this article, we created a Calendar table using DAX and established relationships between the Calendar and Dim_line_name tables and the four Fact tables.
As a result, all six tables are now connected through a Star Schema, completing the data model needed for analysis in Power BI.
By designing the data model correctly, we can analyze multiple Fact tables using the same time dimension or by production line. This data model will serve as the foundation for the KPI calculations and dashboard development covered in the following articles.
Starting with the next article, we will finally begin using DAX to calculate KPIs such as operating profit margin, inventory turnover, and year-over-year changes, moving on to more practical dashboard development.
Coming Up Next
In the next article, we will start using DAX to calculate the KPIs displayed on the dashboard.
In Vol. 5, we’ll finally start using DAX to calculate the KPIs shown on the Dashboard. Using four examples—operating margin, defect rate, equipment utilization rate, and inventory turnover rate—we’ll learn how to create Measures, one of the fundamental building blocks of DAX. More practical DAX calculations, such as year-over-year comparisons, target achievement rates, and cumulative totals, will be covered from Vol. 6 onward.