LOOKUPVALUE DAX with Many to Many relationship

[community_pinki]

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

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[SUMESHKUMAR22]

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]

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]

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

[community_pinki]

Hi @selimovd ,

I have tried above DAX it works...Thanks