What is the Related Function for in Power BI

A bit like LOOKUPs in Excel, how to use it, when to use it

#PowerBI #PowerBIRelated #dataanalytics

https://www.selectdistinct.co.uk/2023/03/15/power-bi-related-function/

1. https://www.bensound.com/ (energy) https://www.bensound.com/ How to use RELATED in Power BI LOOKUP Table A Table B

2. Power BI Tips and Timesavers https://www.bensound.com/ (energy) https://www.bensound.com/ How to use RELATED in Power BI LOOKUP Table A Table B

3. What is the Power BI RELATED function The RELATED function in Power BI is a powerful tool that allows users to quickly and easily access related data from a related table It is similar in many ways to using lookups in Excel (VLOOKUP) In this example we will use the Related function to return a standard cost per mile using a type of vehicle as a lookup reference

4. How to use it You can download the Excel file from our site to follow along

5. Load the Data and set the relationships Vehicle in the Miles tables joins to the Vehicle ID in the Vehicles Table Type in the Vehicles table joins to the Vehicle Type in the Costs Table Vehicle Type ID in the Vehicle Types table joins to the Vehicle Type in the Costs table

6. Add a summary table to the page Drag a new table visual object to the canvas and bring these fields in Vehicle from the Miles table, Vehicle Type Name from the Vehicle Types table, Cost per Mile from the Costs table and Sum of the Distance [mi] from the Miles table

7. Now add the calculated column On the data pane, select the Miles table and click the New Column icon Then paste in this DAX code Mileage Cost = related(Costs[Cost per Mile ])*Miles[Distance [mi]]] press enter and the new column is created

8. The RELATED Function Mileage Cost = related(Costs[Cost per Mile ]) *Miles[Distance [mi]]] This highlighted section is the related function, it requires two parts The Table Name = related( Costs [Cost per Mile ]) And the Field Name = related(Costs [Cost per Mile ] )

9. Errors to avoid The Related function only works when a single value can be returned It cannot work if there is no clear relationship to return a single value, if a relationship returns many possible results it cannot be used The solution there is to use RELATEDTABLE and perform a aggregation, this topic will be covered in a future post so it can be fully explained

10. The Related function is easy to learn, especially if you have some experience of using LOOKUPS in Excel, and can help to simplify your data modelling There is not much to go wrong if you focus on getting the correct relationships between your tables We hope you find this useful