Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Build the correct relationship between tables

Hi all,    I have issues trying to relate a table which hasn't a unique primary key. The situation is the following:    I have the data of user's queries in a software: the resources they are vis...
Show and Tell
Tips and Tricks
Tutorial Requests
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can create a calculated table in activity table to create relation based on user column:

 

User = 
MAXX (
    FILTER (
        'User table',
        'User table'[Role] = EARLIER ( Activities[ROLE] )
            && 'User table'[User]
                IN SELECTCOLUMNS (
                    FILTER (
                        'Query Detail',
                        'Query Detail'[Resource] = EARLIER ( Activities[RESOURCE] )
                    ),
                    "User", [User]
                )
    ),
    [User]
)

 


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello v-lid-msft ,

 

Thank you for your answer and time. I have tried to build your calculated table and It returns me an error in the EARLIER function: 

(I must say the tables on the post were a simplification of the ones I have) I now attach a pic where you will see the real ones

 

 
 

Table users is called 'dUser md', Query detail is 'Query detail [...]' and Activities is 'Table1'. 

 

In Query detail table I have so many columns that I can't show all.. but there's User ID and Workbook (Workbook is the resource)

 

Hope you can still help me, 

 

Many thanks!

 
  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Sorry for my mistake, we are creating a calculated column in Activity Table.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.