Scenario: Calculate sales via inactive relation | DAX | Power BI Interview 🔥

Summary

In this video, the instructor demonstrates how to calculate total sales or revenue in Power BI using an inactive relationship between a fact table and a dimension table. By exploring a report with an active relationship between the 'Order Date' and 'Date' columns, and an inactive relationship between the 'Stock Date' and 'Date' columns, the presenter walks through the process of creating a DAX measure using the `CALCULATE` and `USERELATIONSHIP` functions. This method helps solve the challenge of calculating sales with inactive relationships, providing an insightful tip for Power BI interview scenarios.

Takeaways

• 😀 The video discusses how to calculate total sales or revenue using an inactive relationship in PowerBI.
• 😀 The script demonstrates working with an inactive relationship between the 'date' column in the calendar table and the 'stock date' column in the sales fact table.
• 😀 It starts by showing the active relationship between the 'order date' from the fact table and the 'date' column from the calendar table.
• 😀 The video introduces the 'USE RELATIONSHIP' DAX function as the solution to calculate total sales using an inactive relationship.
• 😀 The DAX formula uses the 'USE RELATIONSHIP' function to define the relationship between the calendar date and the stock date column.
• 😀 The calculation for total revenue with the inactive relationship is performed using the 'CALCULATE' function in DAX.
• 😀 The script walks through the process of creating a new measure in the report view, called 'Total Revenue with Inactive Status'.
• 😀 The video explains how to implement the DAX function to ensure the inactive relationship is taken into account in the calculation.
• 😀 After implementing the formula, the script confirms there are no errors, and the measure returns the correct value for total revenue.
• 😀 The final output shows the total revenue in a card visualization, with the inactive relationship properly applied in the calculation.

Q & A

What is the main topic discussed in this Power BI tutorial?

The main topic is how to calculate total sales or revenue using an inactive relationship between a dimension table (calendar) and a fact table (sales).

What is the difference between active and inactive relationships in Power BI?

An active relationship is the primary relationship between two tables and is used in calculations by default. An inactive relationship is not used in calculations unless explicitly activated, typically using DAX functions like `USERELATIONSHIP`.

Why is it important to know how to handle inactive relationships in Power BI?

It is important because in complex data models, inactive relationships allow you to connect tables in multiple ways. Understanding how to work with them is essential for advanced analysis and answering interview questions related to data modeling.

What is the role of the `USERELATIONSHIP` function in the DAX formula?

The `USERELATIONSHIP` function is used to activate an inactive relationship between two columns, allowing the calculation to take into account that relationship for the specific analysis.

Can you give an example of how the `USERELATIONSHIP` function is applied in this tutorial?

In this tutorial, the `USERELATIONSHIP` function is applied to activate the inactive relationship between the 'Date' column in the calendar table and the 'Stock Date' column in the sales fact table for calculating total revenue.

What is the purpose of the `CALCULATE` function in this scenario?

The `CALCULATE` function is used to modify the context of the calculation, enabling the use of the inactive relationship to adjust how the total revenue measure is computed.

What does the DAX formula `CALCULATE([Total Revenue], USERELATIONSHIP('AW Calendar'[Date], 'AW Sales'[Stock Date]))` do?

This DAX formula calculates the total revenue by activating the inactive relationship between the 'Date' column from the calendar table and the 'Stock Date' column from the sales fact table, allowing for a different calculation context.

What visual is used to display the total revenue in the report?

The total revenue is displayed using a card visual in the Power BI report.

Why is the measure called 'Total Revenue with Inactive Status'?

The measure is named 'Total Revenue with Inactive Status' to reflect that it calculates total revenue based on the inactive relationship between the calendar's date column and the sales fact table's stock date column.

How can this method help during Power BI interviews?

This method can help during Power BI interviews as it demonstrates a practical solution to a common data modeling problem, showing proficiency with DAX functions like `USERELATIONSHIP` and `CALCULATE` to handle complex relationships.