4. The "store size" column should contain only numbers. Remove any text (like the word "qm") from the column so that changing the data type will not produce an error.

Select the "store size" column and choose replace values.
In the "Value to Find" field, enter a space followed by the word 'qm.'
Leave the Replace with field empty.

Note: This will delete everything after the space, including the word 'qm.'

5. Change the data type to a whole number.

Right-click on "store size" column → Change type → Whole number

6. Rename dim_stores to Stores and dim_products to Products.

Create relationships between tables.

1. Make sure the "Auto detect relationship after data is loaded" feature is enabled.

Go to → File → Options and settings → Options → Data load

2. After cleaning and loading the data, the next step is to define relationships between dimension tables (such as Products) and the fact table (such as Sales).

Dimension tables contain descriptive information, while the fact table contains measurable data (e.g., sales).

By creating relationships (usually one-to-many from dimension to fact), Power BI can:

Filter fact data based on dimension selections.
Enable accurate reporting and analysis.
Support proper data modeling using a star schema structure.

Drag & drop the "store id" column from the Sales to the Stores table.

3. New relationship window will appear

Click save

Explained

In the Model view, we see a relationship between the Stores table and the Sales table.

What this relationship shows:

The Stores table is a dimension table.
The Sales table is a fact table.
The relationship is created using the store_id column.

It is a *many-to-one (1 : ) relationship:

1 on the store's side
* (many) on the Sales side
One store can have many sales records.
But each sale record belongs to only one store.

What this means:

Each store_id appears once in the Stores table.
The same store_id can appear many times in the Sales table.
One store can have many sales records.

Filter Direction:

The arrow in the relationship indicates the filter flow direction:
When you select a store (for example, in a slicer),
Power BI filters the related sales records automatically.

Why this is important:

This relationship allows:

Accurate aggregation of revenue, sales, and stock per store.
Filtering reports by store, state, or city.
Proper implementation of a star schema model.

4. Drag and drop "product id" from Products to Sales table → Click Save

What will happen:

A relationship will be created between:

Products (product_id)
Sales (product_id)

It will be a *one-to-many (1 : ) relationship:

1 on the Products side (each product appears once)
* on the Sales side (a product can appear many times in sales)
In short, one product can have many sales

The default cross-filter direction will usually be

Single direction (from Products → Sales)

Cross filter direction in Power BI

Single → Filters flow one way only.

Example: From Products → Sales.
If you filter a product type, it will filter sales, but filtering sales doesn’t filter products.

Both → Filters flow both ways.

Example: From Products ↔ Sales.
Filtering products filters sales, and filtering sales filters products.

Useful for many-to-many relationships or when you want visuals to interact freely in both directions.

Note: Use both carefully, it can slow down your model if you have large tables.

5. Confirm that the relationship between the tables is working correctly.

Go to Report view.
Add a visual (Matrix) under Visualization.
Drag the Type column from the Products table into the visual.
Drag the Order_ID column from the Sales table into the visual.
Change the aggregation of Order_ID to Count.

Note: This will show the number of orders for each product type, confirming that the relationship between the Products and Sales tables is working correctly.

Table and Column Properties in Power BI.

Table Properties.

1. In Model view, select a table and its properties will appear on the right-hand side.

Name: Give clear, descriptive names (e.g., Products, Sales).
Description: Optional notes about the table’s purpose.
Hide/Show: Hide tables used only for calculations to declutter the model.
Synonyms - We define synonyms to help the Q&A feature understand natural language questions better.

People may use different words for the same thing. For example:

Revenue instead of Sales
Orders instead of Order ID
Clients instead of Customers

By adding synonyms, Power BI can correctly interpret user questions and return accurate visuals.

Note: Synonyms improve search accuracy and make Q&A more user-friendly.

Column Properties.

1. In Model view, select a column, and its properties will appear on the right-hand side.

Name: Use readable names (Order_ID → Order ID).
Data Type & Format: Ensure correct type (text, number, date) and formatting (currency, %).
Sort By Column: Control sort order (e.g., months chronologically).
Description: Document column meaning for clarity.
Default Summarization: Set aggregation (Sum, Count, Average).
Hide/Show Column: Hide unnecessary columns from reports.
Synonyms - We define synonyms to help the Q&A feature understand natural language questions better.

2. In Advanced column properties (Model view), you can control how the column behaves in reports and visuals:

What you can configure:

Sort by column

Choose another column to control the sorting order.
Example: Sort Month Name by Month Number.

Data category

Define the type of data for better visuals (e.g., Country, City, Web URL, Image URL).
Helps Power BI understand geographic or special data.

Summarize by

Set the default aggregation (Sum, Average, Count, Don’t summarize).
Controls how the column behaves when added to visuals.

Is nullable

Defines whether the column allows blank (null) values.
Useful for data validation and model integrity.

Note: Advanced properties control sorting behavior, data classification, aggregation, and null handling of a column.

Create a Date table using Power Query and establish a relationship between the Date table and the Sales table.

Important: You can create the Date Table in Power Query or DAX, but pick one method per model, don’t mix both.

Creating a Date Table in Power BI using Power Query is a best practice for time-based analysis.

A date table is the backbone for any reliable time-based reporting in Power BI. It makes calculations, filtering, and visualization accurate and flexible.

Note: Use Power Query if you want a robust, fully customized, and reusable date table.

Note: When you create a Date Table in Power Query, it uses M language (Power Query Formula Language) under the hood.

*
1. In Power Query, right-click on empty space → New Query → Blank Query

2. Enter the following M query in Power Query:

= List.Dates(#date(2017,01,01), 365*7, #duration(1,0,0,0))

Explanation:

= List.Dates(
#date(2017, 1, 1), // Start date (Our sales in sales table starts on 2017, 1, 1)
365 * 7, // Number of days (7 years)
#duration(1, 0, 0, 0) // Step: 1 day

*
Copy M query

3. Convert the list of dates into a table:

Click “To Table” in the Power Query ribbon.
In the dialog that appears, leave the default settings and click “OK”.

Notes:

This transforms the list of dates generated by List.Dates into a proper table.

After this, you can rename the column (e.g., to Date) and start adding Year, Month, Quarter, etc.

4. Rename the column and set the data type:

Rename the column to Date by double-clicking the header or right-clicking → Rename.
Change the column’s data type to Date:
Right-click the column → go to Change Type → select Date.

Why this matters:

Ensures Power BI recognizes the column as a date for time intelligence functions.
Required if you want to mark the table as a Date Table and enable automatic hierarchies.

5. Add additional columns for easier analysis:

In Power Query, go to the Add Column tab.

Add the following columns based on the Date column:

Click on Date column then → Date → Year, Month, Day
Year → Date.Year([Date])
Month → Date.Month([Date])
Day → Date.Day([Date])

Note: You can also add Month Name, Quarter, Weekday, Fiscal Year, or flags like IsWeekend for more advanced reporting.

6. Rename Query1 to Date.

7. Click Close and Apply.

8. You need to create a relationship between:

DateTable[Date] and your fact table date column (for example Sales[OrderDate] or similar)

In Model view:

Drag DateTable[Date]
Drop it onto the date column in your Sales (or other fact) table.

It should be:

One-to-Many (1:*) - means one date can have many sales.
DateTable on the 1 side
Sales on the *** side**
Cross filter direction: Single

After that, your Date table will properly filter Sales, Costs, etc., and time intelligence will work correctly.

Note: This table becomes your time dimension in the model.

Create a Date table using DAX and establish a relationship between the Date table and the Sales table.

Important: You can create the Date Table in Power Query or DAX, but pick one method per model, don’t mix both.

It is recommended to create a separate Date table in Power BI because it enables proper time intelligence calculations and ensures a clean, consistent data model.

Why create a Date table using DAX?

It allows time intelligence calculations (YTD, last year, month-over-month).
It provides a proper Year → Quarter → Month → Day structure.
It ensures all dates are included, even if no sales happened on some days.
It creates a clean, central calendar connected to your data.

Note: A Date table is required for correct and reliable date analysis in Power BI.

Note: Use DAX if you need a quick, simple, or dynamic table based on your fact table dates.

1. Navigate to "Data view" → New table

2. Use the following DAX formula to create a Calendar table:

DateTable = ADDCOLUMNS(CALENDARAUTO(),
"MonthNo", MONTH([Date]),
"MonthName", FORMAT([Date],"MMMM"),
"Quarter", QUARTER([Date]),
"Year", YEAR([Date]),
"Day", DAY([Date]),
"WeekdayNum", WEEKDAY([Date]),
"WeekdayName",FORMAT([Date],"DDDD"))

Copy DAX formula

3. You need to create a relationship between:

DateTable[Date] and your fact table date column (for example Sales[OrderDate] or similar)

In Model view:

Drag DateTable[Date]
Drop it onto the date column in your Sales (or other fact) table.

It should be:

One-to-Many (1:*) - means one date can have many sales.
DateTable on the 1 side
Sales on the *** side**
Cross filter direction: Single

After that, your Date table will properly filter Sales, Costs, etc., and time intelligence will work correctly.

Note: This table becomes your time dimension in the model.

In conclusion, creating a separate Date table and linking it to the Sales table ensures accurate time intelligence calculations and a well-structured, professional data model.

Mark a Date table as a Date Table in Power BI.

You mark a table as a Date Table in Power BI to tell the system: “This is my official calendar for time-based analysis.” It’s a small step but very important.

Marking a Date Table ensures accurate time intelligence, proper relationships, and automatic hierarchies. Without it, your date-based calculations could fail or be inconsistent.

1. Right-click on Date table, then click "Mark as date table."

2. Turn on "Mark as a date table" and choose the date column.

Create data granularity by defining a relationship between two previously unrelated tables: the Date table and the Cost table.

1. In this example, I used the following DAX-generated date table.

The table is also marked as a Date table.

Copy DAX query

DateTable = ADDCOLUMNS(CALENDAR(DATE(2017,1,1),DATE(2018,12,31)),
"MonthNo", MONTH([Date]),
"MonthName", FORMAT([Date],"MMMM"),
"Quarter", QUARTER([Date]),
"Year", YEAR([Date]),
"Day", DAY([Date]),
"WeekdayNum", WEEKDAY([Date]),
"WeekdayName",FORMAT([Date],"DDDD"))

Note: You can adjust the dates as needed.

2. In the Cost table, the granularity (level of detail) is based only on Year and Month, so it does not match the daily granularity of the Date table.

To resolve this, we should create a Year–Month column in both tables and use it to establish the relationship.

Cost table

Date table

Cost table

1. Create a Year–Month column in both tables and use it to establish the relationship.

Go to Power Query editor

2. Go to the Cost table and use the existing columns (Year and MonthNo) to create a common column.

Select both columns by holding the Ctrl key.
Go to the Add Column tab.
Click Column from Examples.
Select From Selection.

Note: When you click Column from Examples and choose From Selection, Power Query automatically generates a new column based on the pattern you type, using only the selected columns. This allows you to quickly create a combined Year–Month column without writing a custom formula.

3. Create a new column combining Year and Month in Cost table.

Enter an example value: 2021-4 (where 2021 represents the year and 4 represents the month).

Power BI will automatically detect the pattern and generate the new column for all rows.

4. Rename the column to YearMonth.

Date table

1. Create a new column combining Year and Month in the Date table.

A DAX-generated table exists only in the Table view, you can use it in relationships and measures, but it cannot be edited in Power Query.

Go to the Table view and modify the DAX query to create a column that matches the Year–Month format of the Cost table.
Add this line to the DAX query: "Year-Month", FORMAT([Date], "YYYY-M")

Note: Use Shift + Enter → this moves the cursor to a new line without executing the formula.

Create a relationship between the cost table and the date table in the model view.

1. Go to model view and create a relationship between the cost table and the date table using common columns.

Drag and drop YearMonth column from costs table to Year-Month in the DateTable.

2. Set the cardinality between the costs table and the date table to One-to-Many (1:*) and the Cross filter direction to Both.

One-to-Many (1:*) - The Cost table can have multiple entries per month (many rows).

Both - Using Both ensures that filters work in both directions, so your charts and calculations update correctly whether you filter by Date or by Cost.

Create model calculations using DAX measures and calculated columns.

Important: Calculated columns store values in the model and take up additional memory, so creating them should be your last choice. Prefer using measures whenever possible.

Use a calculated column only when you need row-by-row calculations that must be stored in the table (for relationships, sorting, or filtering at the row level).

*
Create a calculated column to calculate revenue.

1. Make sure that the price column is in the decimal number data type.

Note: Change the data type in Power Query if needed.

2. Select the Sales table, then click New Column, or right-click on Sales and choose New Column.

3. Calculate revenue by multiplying the price column with the sales column.

Select Sales table → New column → Column tools tab → enter DAX query
Use the following query: Revenue calculated = Sales[price] * Sales[sales] Copy query

This creates a calculated column called "Revenue" calculated in the Sales table.

Here’s what it does:

Sales[price] – takes the price of each product in the Sales table.
Sales[sales] – takes the quantity sold for that product.
* – multiplies the two values row by row.

The result is stored in a new column (Revenue calculated) showing the total revenue for each sale.

Note: Because this is a calculated column, the value is computed and stored for every row in the table, which can increase the model size if the table is large.

4. Visualize the calculated column:

Choose a table visual.
Drag and drop the Order ID column and Revenue calculated into the visual.
order id - Don't summarize

As you can see in the screenshot, the Table visual displays the Revenue calculated column alongside the order ID. Each row shows the revenue for that specific order, and the Total at the bottom sums up all the revenue values across the table. This makes it easy to verify that the calculated column is working correctly and aggregating the data as expected.

Note: To hide zero values in your calculation, use the following DAX:

Revenue calculated =
VAR RevenueValue = Sales[price] * Sales[sales]
RETURN
IF(RevenueValue = 0, BLANK(), RevenueValue)

Copy DAX query

Create measures to calculate revenue.

A measure is generally preferable to a calculated column in most reporting scenarios.

Measures are calculated dynamically based on the filter context (they react to slicers, filters, and visuals).

They do not increase the model size because they are not stored in memory.

They are more efficient for aggregations (SUM, COUNT, AVERAGE, etc.).

*
1. Select the Sales table, then click New Column, or right-click on Sales and choose New Measure.

2. Calculate revenue by multiplying price column with sales column.

Select Sales table → New column → Column tools tab → enter DAX query

Use the following query:

Revenue Measure =
IF(
SUMX(Sales, Sales[price] * Sales[sales]) = 0,
BLANK(),
SUMX(Sales, Sales[price] * Sales[sales])
)

Copy DAX query

What this does:

SUMX calculates total revenue dynamically.
If the result equals 0, it returns BLANK() instead.
Power BI visuals automatically hide BLANK(), so zero values will not appear.

3. Visualize the measure:

Choose a Table visual.
Drag and drop the Order ID column and the Revenue calculated measure into the visual.
Set Order ID to Don’t summarize.

As you can see, we created two identical visuals, one using a calculated column and the other using a measure.

Create calculations using the CALCULATE function in Power BI (DAX).

The CALCULATE function allows you to:

Modify the filter context of a calculation.
Apply custom filters to focus on specific data.
Ignore existing filters when performing calculations.

1. For this example we want to limit the calculation only for the year 2018.

Drag and drop the Year column from the date table to the visual.
Set Year to Don’t summarize.

Note: Currently, when you click on 2017 or 2018, the data in both the calculated column and measure visuals updates automatically according to the selected year.

2. Limit the calculation only for the year 2018.

Create a CALCULATE measure by right-clicking the Sales table and selecting New Measure.

3. Name the measure "Revenue for 2018"

Use the following query: Revenue for 2018 = CALCULATE([Revenue Measure], DateTable[Year] = 2018) Copy query

4. Now, when you visualize the "Revenue for 2018" measure, it will display results only for the year 2018.

Drag and drop the Revenue for the 2018 measure into the visual.
Change the visual type to a Table.

It will show revenue only for 2018 (52,097.95).

Create a Year-to-Date (YTD) revenue calculation using Time Intelligence in Power BI.

Use DAX time intelligence functions to analyze data across various date and time dimensions, such as year, quarter, month, or day.

1. Click the "+" sign at the bottom to open a new page.

2. Drag and drop the Revenue measure from the Sales table and the Date column from the DateTable into the visual.

Choose a Line Chart.

Currently, the chart shows revenue for each specific date.

To analyze Year-to-Date (YTD) revenue, you need to use a time intelligence function, which allows cumulative calculations over time periods.

3. Create a time intelligence function to analyze Year-to-Date (YTD) revenue.

Right-click on Sales table, then select "New measure"

4. We will use aggregated "Revenue measure" created earlier.

Name the measure Year-to-Date revenue
Use the following query: Year-to-Date revenue = CALCULATE([Revenue Measure], DATESYTD(DateTable[Date])) Copy query
Drag and drop "Year-t-Date revenue" to the Y-axis.

The line chart compares daily revenue with Year-to-Date (YTD) revenue:

The light blue line shows revenue for each specific date.
The dark blue line shows cumulative revenue for the year, calculated using a time intelligence YTD function.

By tracking both lines, you can see daily revenue fluctuations and overall revenue growth throughout the year. The YTD line resets at the start of each year, reflecting the cumulative total from January 1st.

5. Compare results with the previous year using DATEADD.

Uncheck the "Year-to-Date revenue"

Create a new measure and use the following query: Previous year revenue = CALCULATE([Revenue Measure], DATEADD(DateTable[Date], -1 , YEAR)) Copy query

Now you can see that the dark blue line represents the year 2017.

Optimize model performance using performance analyzer.

Identify poorly performing measures, visuals, and relationships.

1. Before using the Performance Analyzer, clear all caches by creating a new blank page, saving the PBIX file, and reopening it.

2. When you restart Power BI, it will open on Page 3 with the cache cleared.

Go to the View tab → Performance Analyzer → Start recording then navigate to page two

3. Interact with the visuals on Page 1 and Page 2 (for example, apply filters, slicers, or change selections) to capture performance data.

Once you finish testing the interactions, click Stop Recording in the Performance Analyzer pane to review the results.

What the Performance Analyzer Results Show?

From the screenshot:

Revenue Measure and Previous Year Revenue visual
Total duration: 564 ms
DAX query: 48 ms
Visual display: 43 ms
Other: 473 ms
Several table visuals range between ~28 ms and ~562 ms.
There are multiple cross-highlighted events, meaning visuals are interacting with each other.

Explanation of the Results

DAX query (48 ms)

This is the time required to execute the DAX calculation in the data model.
48 ms is very good the measure is not the bottleneck.

Visual display (43 ms)

Time required to render the visual on the screen.
Also, fast rendering is not the main issue.

Other (473 ms)

This is the largest portion of time. It usually includes:
Visual interactions
Data preparation
Cross-filtering / cross-highlighting
Internal Power BI processing

This is where most of the delay is happening.

Table visuals (~562 ms for one table)

A table taking over 500 ms suggests:
Too many rows displayed
Too many columns
Complex measures inside the table
Heavy cross-filtering interactions

What Can Be Done (Optimization Ideas)

1. Reduce Table Size

Limit number of columns
Avoid showing unnecessary detailed rows
Use summarized data when possible

2. Reduce Visual Interactions.

Disable unnecessary cross-filtering between visuals
(Format → Edit interactions)

3. Optimize Measures

Avoid complex iterators if not needed (SUMX, FILTER inside FILTER)
Use variables (VAR) in DAX
Prefer measures over calculated columns

4. Reduce Number of Visuals per Page

Too many visuals increase “Other” processing time.

5. Check Data Model

Ensure proper relationships
Star schema (Fact + Dimension tables)
Avoid bidirectional filtering unless required

Conclusion

This guide demonstrated how to properly model data in Power BI by building structured relationships, creating and marking a Date table, defining correct data granularity, and implementing DAX calculations including CALCULATE and Year-to-Date (YTD) Time Intelligence.

A well-designed data model is the foundation of accurate reporting and strong performance. By applying star schema principles, using measures effectively, and analyzing performance with Performance Analyzer, we ensure that reports are scalable, efficient, and reliable.

Strong modeling leads to better insights and better insights lead to better decisions.

Written by Kirill.A - Azure & Cybersecurity Consultant at AntusNet

➤ Want more? Browse all our Azure implementation guides.

Need help implementing secure Azure solutions?

Contact us for a free consultation.

Azure Quick Links

Part 2

Model the Data with power BI

Designed and optimized a data model with relationships, Date tables, and DAX measures; drove YTD revenue analysis using Time Intelligence, leveraged CALCULATE for complex filters, and enhanced performance via Performance Analyzer..

➤ Want more? Browse all our Azure implementation guides.

Need help implementing secure Azure solutions?

Contact us for a free consultation.

Looking for a free quote?

Company

Quick Link

Contact Us

Model the Data with power BI - antusnet.ca

Azure Quick Links

Part 2

Model the Data with power BI

Designed and optimized a data model with relationships, Date tables, and DAX measures; drove YTD revenue analysis using Time Intelligence, leveraged CALCULATE for complex filters, and enhanced performance via Performance Analyzer..

➤ Want more? Browse all our Azure implementation guides.

Need help implementing secure Azure solutions?

Contact us for a free consultation.

Looking for a free quote?

Company

Quick Link

Contact Us