cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Help with relationship

 

Hi

I hope you can help

I have a BI page which is linking multiple tables together to show trends over time as well as current numbers.

 

I have 3 tables (as shown in picture): Table 6; MatrixbyWeek; DatabaseTable.

To link these together (due to repetitions in the data) I have 3 "bridge" tables: Team List; Week Commencing; Parent/Child.

 

Most of these links work, i.e. Parent / child filters all the tables correctly as does the team list. However I cannot get Table 6 and Week commencing to work together. (Week commencing works with all other aspects)

 

I would like this link to provide me a table showing count of "Week commencing - Opened" and "Week commencing - closed" which can be filtered by a slicer driven by the bridge table "Week Commencing". All dates are calculated columns to return the monday of the week, therefore the formats should all match.

 

I have colour coded the picture so you can see which tables are linked

 

Thanks for your help

 

Matt

 

 Power BI help.PNG

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @Matt22365,

 

I think you could establish two inactive relationships between 'Week commencing(2)' and Table6, one is from [Week commencing] to [Week commencing - Opened], the other is from [Week commencing] to [Week commencing - closed]. When calculating the count of "Week commencing - Opened" and "Week commencing - closed" based on slicer selection, you can use USERELATIONSHIP function in measure formula.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

4 REPLIES 4
Microsoft
Microsoft

Hi @Matt22365,

 

I think you could establish two inactive relationships between 'Week commencing(2)' and Table6, one is from [Week commencing] to [Week commencing - Opened], the other is from [Week commencing] to [Week commencing - closed]. When calculating the count of "Week commencing - Opened" and "Week commencing - closed" based on slicer selection, you can use USERELATIONSHIP function in measure formula.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

Hi @v-yulgu-msft

 

Thank you for your help so quickly. I have not used USERELATIONSHIP before. Do I create a new column with this formula and then use this column in the slicer?

 

Thanks

 

Matt 

Hi @v-yulgu-msft

 

I have attempted your solution and made the following measure in table6:

Measure = Calculate(COUNT(Table6[Week Commencing - Opened]), USERELATIONSHIP('Weekcommencing (2)'[Week Commencing].[Date],Table6[Week Commencing - Opened]))

 

However I get the error:

"USERELATIONSHIP function can only use the two columns references participating in relationship"

 

I have 2 inactive relationships between table6 and Weekcommencing (2), (both ways)

 

Any ideas?

 

Matt

Hi

 

Ive managed to fix it, thanks for your help

 

The fix was not enough brackets, corrected version below:

Opened by W/C = CALCULATE(COUNT(Table6[Week Commencing - Opened]), (USERELATIONSHIP(Table6[Week Commencing - Opened],'Weekcommencing (2)'[Week Commencing])))

 

Matt

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors