Forum Discussion

Matt22365's avatar
Matt22365
Resolver III
7 years ago
Solved

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

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Matt22365
      Resolver III
      7 years ago

      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's avatar
        Matt22365
        Resolver III
        7 years ago

        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