Forum Discussion

community_pinki's avatar
community_pinki
Icon for Helper II rankHelper II
5 years ago
Solved

LOOKUPVALUE DAX with Many to Many relationship

Hi All,

I want to retrive ImageURL column from creators image table to creator data table using LOOKUPVALUE() Dax
and tables are connected many to many relationship with CastID column.

 

 

 

Any Help would be Appeciated...

 

 

 

 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey community_pinki ,

     

    when you have a many to many relationship a lookup will not give you a clear answer.

    It's like I ask you "It's January, what year is it?". 

     

    But you can for example do a MAX of the URL:

    ImageURL Column =
VAR vCastID = Creator_Data[CastID]
RETURN
    CALCULATE(
        MAX( Creator_ImageTable[Image URL] ),
        Creator_ImageTable[CastID] = vCastID
    )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

5 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey community_pinki ,

     

    when you have a many to many relationship a lookup will not give you a clear answer.

    It's like I ask you "It's January, what year is it?". 

     

    But you can for example do a MAX of the URL:

    ImageURL Column =
VAR vCastID = Creator_Data[CastID]
RETURN
    CALCULATE(
        MAX( Creator_ImageTable[Image URL] ),
        Creator_ImageTable[CastID] = vCastID
    )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • SUMESHKUMAR22's avatar
      SUMESHKUMAR22
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi selimovd ,

      • In the same above problem statement if I have relationship based on 2 columns ( e.g caste id & caste name ) and its many to many but 1 is active and another is inactive ) then what changes in calculate column should give us the result?

      Thanks in advance.

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        Hey SUMESHKUMAR22,

         

        you can use USERELATIONSHIP within the calculate function in addition to change the active relationship.

         

        If you have further questions it's better to open a new topic as it's a new case.

         

        Best regards

        Denis