Welcome back to Virvijay.com, where we break down Power BI concepts into easy-to-understand tutorials! 🎯
In our last blog, we explored IF and SWITCH functions to apply conditional logic. Today, we’re diving into two powerful DAX functions that help in combining data from multiple tables:
- ✅ RELATED - Used to fetch data from a related table in a one-to-many relationship
- ✅ LOOKUPVALUE - Used to retrieve specific values when relationships do not exist
By the end of this blog, you’ll learn:
- ✔ How to use RELATED to fetch data from another table
- ✔ How to use LOOKUPVALUE when tables are not related
- ✔ Real-world applications of these functions
- ✔ Best practices to improve performance
1️⃣ Understanding RELATED Function in Power BI
The RELATED function is used when two tables have an existing relationship in Power BI’s model. It helps in pulling values from a parent table (one-side) to a child table (many-side).
👉 Syntax:
DAX
RELATED(ColumnName)
📌 Example 1: Fetching Product Category into Sales Table
Let's say we have two tables:
- ✔ Products Table (Product ID, Product Name, Category)
- ✔ Sales Table (Sales ID, Product ID, Sales Amount)
If these tables are related through Product ID, we can pull the Category into the Sales Table using:
👉 DAX Formula:
DAX
Product Category = RELATED(Products[Category])
🚀 How it works:
- ✔ Fetches the category of each product into the Sales table
- ✔ Works only when there is a relationship between the tables
2️⃣ Using LOOKUPVALUE to Retrieve Data Without Relationships
The LOOKUPVALUE function is used when two tables are not related but you still need to fetch data from one table to another.
👉 Syntax:
DAX
LOOKUPVALUE(Result_Column, Search_Column, Search_Value)
📌 Example 2: Finding Employee Department Without a Relationship
Suppose we have:
- ✔ Employee Table (Emp ID, Name, Department)
- ✔ Sales Table (Sales ID, Emp ID, Sales Amount)
If these tables are not linked, but we still need to fetch the department of each employee into the Sales table, we use:
👉 DAX Formula:
DAX
Employee Department = LOOKUPVALUE(Employee[Department], Employee[Emp ID], Sales[Emp ID])
🚀 How it works:
- ✔ Searches the Employee table for matching Emp ID
- ✔ Retrieves the corresponding Department
- ✔ Works even if tables are not related
3️⃣ When to Use RELATED vs LOOKUPVALUE?
- 🚀 If the tables are related → Use RELATED
- 🚀 If there’s no relationship → Use LOOKUPVALUE
4️⃣ Real-World Applications
🚀 Use Case 1: Bringing Customer City into Sales Table
- ✔ Customers Table → (Customer ID, Name, City)
- ✔ Sales Table → (Sales ID, Customer ID, Sales Amount)
👉 Using RELATED:
DAX
Customer City = RELATED(Customers[City])
🚀 Use Case 2: Fetching Loan Type Based on Loan ID
- ✔ Loans Table → (Loan ID, Loan Type, Interest Rate)
- ✔ Applications Table → (Application ID, Loan ID, Applicant Name)
👉 Using LOOKUPVALUE:
DAX
Loan Type = LOOKUPVALUE(Loans[Loan Type], Loans[Loan ID], Applications[Loan ID])
5️⃣ Common Mistakes and Best Practices
🚨 Mistake 1: Using LOOKUPVALUE When a Relationship Exists
❌ Wrong:
DAX
LOOKUPVALUE(Products[Category], Products[Product ID], Sales[Product ID])
✅ Better:
DAX
RELATED(Products[Category])
- ✔ Use RELATED when tables are already linked.
🚨 Mistake 2: Using RELATED in Unrelated Tables
❌ Wrong:
DAX
RELATED(Employee[Department])
✅ Correct:
DAX
LOOKUPVALUE(Employee[Department], Employee[Emp ID], Sales[Emp ID])
- ✔ Use LOOKUPVALUE if no relationship exists.
🚨 Mistake 3: Ignoring Performance Optimization
- ✔ RELATED is faster than LOOKUPVALUE because relationships are indexed.
- ✔ Too many LOOKUPVALUE calls slow down reports.
6️⃣ Conclusion: Choosing the Right Function
🚀 By mastering these functions, you can efficiently fetch data across tables in Power BI.
7️⃣ What’s Next?
📌 In the next blog, we’ll cover:
- ✅ Understanding CALCULATE: The Most Powerful DAX Function
- ✅ How to Apply Filters Dynamically in Measures
- ✅ Real-World Examples & Best Practices
🔔 Stay tuned to Virvijay.com for more Power BI tutorials!
📩 For any queries, reach out to support@virvijay.com
💬 Got questions? Drop them in the comments!
