13. DAX: Data Analysis Expressions
13.11 RELATED and RELATEDTABLE in Power BI DAX with Example
Quick answer: the short, self-contained version of this lesson is the guide DAX RELATED and RELATEDTABLE.
RELATED in Power BI DAX with example — in 30 seconds
What: RELATED brings a value from the "one" side table into a row of the "many" side table.
Why: Orders has Qty. Product has Unit Price. Line value needs both on the same row.
Tiny example: BLK-1: 2 × ₹30 = ₹60. Total line value = ₹235.
RELATED in Power BI DAX with example — 30 सेकंदात
काय: RELATED "one" बाजूच्या table मधली value "many" बाजूच्या table च्या row मध्ये आणतो.
का: Orders मध्ये Qty आहे. Product मध्ये Unit Price आहे. Line value साठी दोन्ही एकाच row वर हवेत.
छोटं उदाहरण: BLK-1: 2 × ₹30 = ₹60. एकूण line value = ₹235.
RELATED in Power BI DAX with example — 30 सेकंड में
क्या: RELATED "one" side की table की value "many" side की table की row में लाता है.
क्यों: Orders में Qty है. Product में Unit Price है. Line value के लिए दोनों एक ही row पर चाहिए.
छोटा उदाहरण: BLK-1: 2 × ₹30 = ₹60. कुल line value = ₹235.
First look at the data (before → after):
Same before / after tables as text
Before — Orders (many side)
| Order ID | Product ID | Qty |
|---|---|---|
| BLK-1 | P-021 | 2 |
| BLK-2 | P-041 | 1 |
| BLK-3 | P-021 | 3 |
About this data: This table uses columns: Order ID, Product ID, Qty. This table uses columns: Product ID, Unit Price (₹). This table uses columns: Order ID, Qty, Unit Price, Line Value (₹). Values shown are the lesson examples already on this page (not a new dataset).
Before — Product (one side)
| Product ID | Unit Price (₹) |
|---|---|
| P-021 | 30 |
| P-041 | 85 |
Formula / click path
Line Value = Orders[Qty] * RELATED(Product[Unit Price])
(needs an active Product 1 → * Orders relationship)
After — New column in Orders
| Order ID | Qty | Unit Price | Line Value (₹) |
|---|---|---|---|
| BLK-1 | 2 | 30 | 60 |
| BLK-2 | 1 | 85 | 85 |
| BLK-3 | 3 | 30 | 90 |
Do it step by step:
- Check the relationship Product[Product ID] 1 → * Orders[Product ID] in Model view.
- Select Orders. Table tools › New column.
- Type the Line Value formula.
- Total of the column = ₹235. In a measure, prefer
SUMX(Orders, Orders[Qty] * RELATED(Product[Unit Price])).
एक-एक step करून बघा:
- Model view मध्ये Product[Product ID] 1 → * Orders[Product ID] relationship तपासा.
- Orders निवडा. Table tools › New column.
- Line Value formula type करा.
- Column चा total = ₹235. Measure मध्ये
SUMX(Orders, Orders[Qty] * RELATED(Product[Unit Price]))वापरणं चांगलं.
एक-एक step करके देखिए:
- Model view में Product[Product ID] 1 → * Orders[Product ID] relationship जाँचिए.
- Orders चुनिए. Table tools › New column.
- Line Value formula type कीजिए.
- Column का total = ₹235. Measure में
SUMX(Orders, Orders[Qty] * RELATED(Product[Unit Price]))बेहतर है.
Both use existing relationships (संबंध) and are mostly used in calculated columns or iterators.
RELATED(<column>)– fetches a value from the one side (lookup, like VLOOKUP). Used on the many side.RELATEDTABLE(<table>)– returns the related rows from the many side. Used on the one side.
-- Calculated column in Orders (many side): bring Category from Product
Category = RELATED(Product[Category])
-- Calculated column in Orders: city of the dark store that served the order
Store City = RELATED(DarkStore[City])
-- Calculated column in Customer (one side): count this customer's order lines
Customer Order Lines = COUNTROWS(RELATEDTABLE(Orders))
-- Calculated column in DeliveryPartner: number of orders delivered by this partner
Partner Orders = CALCULATE(DISTINCTCOUNT(Orders[Order ID])) -- context transition
LOOKUPVALUE
LOOKUPVALUE(<result column>, <search column>, <search value>) can find a value without a relationship. It is useful occasionally, pan relationships + RELATED are faster and clearer.
Ravindra Bagale's Tip
A common mistake is using RELATED in a measure without a row context, or from the one side of a relationship. RELATED works in calculated columns or inside iterators (SUMX over Orders) on the many side. From the one side, use RELATEDTABLE. Remember this rule.
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे row context शिवाय measure मध्ये, किंवा relationship च्या one side कडून RELATED वापरणं. RELATED हे calculated columns मध्ये किंवा many side वरच्या iterators मध्ये (Orders वर SUMX) चालतं. One side कडून RELATEDTABLE वापरा. हा नियम लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
एक common गलती है row context के बिना measure में, या relationship की one side से RELATED इस्तेमाल करना. RELATED calculated columns में या many side पर iterators के अंदर (Orders पर SUMX) काम करता है. One side से RELATEDTABLE इस्तेमाल करो. यह नियम याद रखो.
Loading DAX practice tasks...
- Shop city on every sale (Easy): Calculated column on Sales: Shop City = the City of the shop that made the sale.
- Shop country on every sale (Easy): Calculated column on Sales: Shop Country = the Country of the related shop.
- Line revenue column (Easy): Calculated column on Sales: Line Revenue = Cups × Price.
- Is it a Scottish sale? (Medium): Calculated column on Sales: Is Scotland = TRUE when the related shop Country is "Scotland".
- Number of sales per shop (Medium): Calculated column on Shop: Sale Count = number of Sales rows for that shop.
- Cups per shop (Medium): Calculated column on Shop: Shop Cups = total Cups of the related Sales rows.
- Revenue per shop column (Medium): Calculated column on Shop: Shop Revenue = Cups × Price added over the related Sales rows.
- London revenue measure (Hard): Measure: London Revenue = Cups × Price added for sales whose shop City is "London". Use FILTER on Sales with RELATED.
- England cups with IF (Hard): Measure: England Cups = add Cups only when the related shop Country is "England", else add 0.
- Busy shops per city (Hard): Shown per City. Measure: Busy Shops = number of Shop rows whose related Sales count is 6 or more.
Revision card
Comparison card