Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PPStar
Helper V
Helper V

How to use Related to get data from another inactive relationship

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
PPStar_0-1715254786471.png

 

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

PPStar_1-1715254858073.png

 

What am i doing wrong?

 

2 ACCEPTED SOLUTIONS
Syk
Super User
Super User

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.

View solution in original post

v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1717393644754.png

Date

vheqmsft_1-1717393660004.png

ID

vheqmsft_2-1717393673467.png

Relationship

vheqmsft_3-1717393704269.png

Create a measure

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

Final output

vheqmsft_4-1717393779483.png

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

 

 

 

View solution in original post

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1717393644754.png

Date

vheqmsft_1-1717393660004.png

ID

vheqmsft_2-1717393673467.png

Relationship

vheqmsft_3-1717393704269.png

Create a measure

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

Final output

vheqmsft_4-1717393779483.png

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
Super User
Super User

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.