Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

RELATED doesn't work for One-to-Many

Dear all,    I'm trying to lookup some values from anoter table but encountered with below error.    Dax used for the calculated column: Com = RELATED('Comissions&Margin'[Comissions])   Error M...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    v-lili6-msft, I have resolved this issue by changing the data connection to "Import"

     

    v-lili6-msft wrote:

    hi, Anonymous 

    This issue had been was reported to the product group before. ICM: 100120097

    PG team already created a bug item to track this issue. 

    PG team will try to add comments or a new article to clarify the related function work in certain scenarios.

     

    you could use the solution I had provided above for a substitution for now.  :smileyvery-happy:

     

    And could you please tell me if you have other problem? If not, could you please mark the helpful replies as Answered?

     

    Best Regards,

    Lin


    . Thank you. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, Stachu Thanks for reply! It is one direction from table Comissions&Margin to table Sales. There are no other tables linked to both of them either which is pretty straight forward. I saw other related posts mentioning "RELATED" is only USABLE for 1-1 relationship, this could be one of the reason that causes the error and hence I'm seeking for alternatives. 

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

HI, Anonymous 

Based on my research, for your case, it should be that one of the two table is import(or is created by new table) and another is directquery.

So just use this LOOKUPVALUE Function for it:

Column 2 = LOOKUPVALUE('Comissions&Margin'[Comissions],'Comissions&Margin'[SalesCode],'Table'[SalesCode])

Then it should work well.

 

Best Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks, v-lili6-msft . You're right, one of the table is imported while the another one is direct query. I did tried on "LOOKUPVALUE" as well actually but it turns out with this error "A calculated column or RLS expression on a DirectQuery table cannot reference tables from a different data source." 

    It looks like there are no way to resolve this if we were to maintain one of the table as "DirectQuery". 

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      hi, Anonymous 

      In my side, It works well. and if you could try this formula:

      Column = CALCULATE(SUM('Comissions&Margin'[Comissions]),FILTER('Comissions&Margin','Comissions&Margin'[SalesCode]='Table'[SalesCode]))

      Best Regards,

      Lin

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks, v-lili6-msft , this could be helpful but I'm still clueless why it doesn't work for direct query for my case. 

         

        v-lili6-msft wrote:

        hi, Anonymous 

        In my side, It works well. and if you could try this formula:

        Column = CALCULATE(SUM('Comissions&Margin'[Comissions]),FILTER('Comissions&Margin','Comissions&Margin'[SalesCode]='Table'[SalesCode]))

        Best Regards,

        Lin