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. 

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

based on what you posted it should work fine as you wrote it

 

can you post the screenshot of the relationships from the PowerBI? is it single direction or bidirectional? are there any other joins that may cause relationship ambiguity?

  • 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".