How to use Related to get data from another inactive relationship

[PPStar]

Hello. 

 I have a table called All Surveys which has data about completed surveys. It has a Completed Date

I also have a Data Table with the regular Date columns, e.g Date

I am unable to have an active relationship from Date[Date] and AllSurvery[Completed Date] as Date has a 1:M relationship with another table that is also related to All Surveys

So i have created an Inactive Relationship with Date and All Surveys. Its a 1:M relationship Date(1)  : CompletedDate(M)

What i need is (a new column in the All Survery which shows the Month Year from the Date Table. 

I created a new column in the All Survery Table as : 

Column = RELATED('Date'[Month & Year])

When i put the Column into table, i am shown the below

It is missing the Month May 2024. 

The All Survery definaltey has May Data becuase if i Plot a table with CompletedDate from All Surveys, i get the below

What am i doing wrong?

[Syk · Accepted solution]

You may be able to use the USERELATIONSHIP() function in dax. https://learn.microsoft.com/en-us/dax/userelationship-function-dax

Although, this sounds like something you could also do in Power Query - merging the table and extracting your needed row.

[Anonymous · Accepted solution]

Hi @PPStar ,
Thanks for @Syk reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
All Survery

Date

ID

Relationship

Create a measure

Measure = 
CALCULATE(
    MAX('Date'[Month&Year]),
    USERELATIONSHIP('All Survery'[Completed Date],'Date'[Month&Year])
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Syk · Accepted solution]

You may be able to use the USERELATIONSHIP() function in dax. https://learn.microsoft.com/en-us/dax/userelationship-function-dax

Although, this sounds like something you could also do in Power Query - merging the table and extracting your needed row.