Calculated Column Based on Relationships in Three Tables

[kp_wood13]

I am attempting to populate a column in table 'DB List'[child] when the SELECTEDVALUE matches 'ServicesToHosts'[child]. However I also then need to use the relationship between 'ServicesToHosts'[parent] and 'ServicesList'[name] in order to return 'ServicesList'[u_mc_rating] values to the original table 'DB List'. I can't strictly use relationships because it causes a circular reference in the visuals. I've tried nested IF statements and various CALCULATE statements but had no luck to date, the visual continues to show all results (like below). Any suggestions?

[v-pnaroju-msft · Accepted solution]

Thank you, @lbendlin, for your response.

Hi @kp_wood13,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, to achieve the goal of pulling ServicesList[u_mc_rating] into DB List, kindly follow the workaround process of using a two-step DAX approach with LOOKUPVALUE to simulate relationships without causing circular dependencies:

1. In the DB List, retrieve the parent from ServicesToHosts:
   
   

   ParentFromHosts =

   LOOKUPVALUE(

       'ServicesToHosts'[parent],

       'ServicesToHosts'[child], 'DB List'[child]

   )

2. Use the parent to fetch  u_mc_rating from ServicesList:
   
   

   u_mc_rating =

   LOOKUPVALUE(

       'ServicesList'[u_mc_rating],

       'ServicesList'[name], 'DB List'[ParentFromHosts]

   )

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who are facing similar queries.

Thank you.

[lbendlin]

SELECTEDVALUE has no meaning for calculated columns.

Consider using TREATAS if your relationships are not helpful for that query.