Search code examples
powerbidaxdata-analysispowerbi-desktopmeasure

Can I use RELATED for a table that's connected to another table?

I have three tables. Shift Activity - Night is my main data table. That's connected to Engineers via Engineer Name That Engineer table is connected to Service Desk Rota via Engineer Full Name.

enter image description here

I need to write some dax to add a column to add either Shift Date or Shift Type (from Service Desk Rota) in the Shift Activity - Night table.

I've tried using RELATED but the Service Desk Rota isn't a table in the suggested, implying it's not related?

enter image description here

Does anyone know how I could go about solving this?

Solution

  • The problem you have is a logical one rather than a DAX one. RELATED() gets a single value (which works for the Engineers table) but then you would need RELATEDTABLE() to get the multiple values in the jump from Engineers to Service Desk Rota. i.e. There are many shift dates for each Engineer and not a single value.

    Try something like this to get all the values.

    Column = 
 CALCULATE(
 CONCATENATEX('Service Desk Rota', 'Service Desk Rota'[Shift Date], ", "), 
 'Service Desk Rota'[Engineer Full Name] =  RELATED(Engineers[Engineer Full Name]))