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

SUMESHKUMAR22's avatar
SUMESHKUMAR22
Icon for Helper IV rankHelper 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