Ravindra BagaleCourses & study guides

13. DAX: Data Analysis Expressions

13.11 RELATED and RELATEDTABLE

By Last updated:

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.