Forum Discussion

community_pinki's avatar
community_pinki
Helper 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.     ...
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
     
selimovd's avatar
selimovd
Most 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
    Helper 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
      Most 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

      • SUMESHKUMAR22's avatar
        SUMESHKUMAR22
        Helper IV
        4 years ago

        Hi selimovd ,

        Perfect I got your point. But it would be very helpful if you could mention the change in the required in this case w.r.t to the above calculated column that you mentioned. 
        In my case its the same many to many (active) but if I take one more many-many (inactive) based on caste name. So for this where exactly in the userrelationship function should  be used in the below measure:
        CALCULATE( MAX( Creator_ImageTable[Image URL] ), Creator_ImageTable[CastID] = vcastID

        Thanks in advance