Help with relationship

[Matt22365]

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

 

[v-yulgu-msft · Accepted solution]

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

[Matt22365]

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 

[Matt22365]

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

[Matt22365]

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