cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MunroA7
Advocate III
Advocate III

Systemuser table mutiple relationships

I'm using 2016 update 1 CRM Online with Power BI connected via HTTP REST API to boost speed.

 

I'm using the 'best practice' protocol of not using expanded columns in the data query but rather using a relationship & look up / related column in another table to boost performance when returning rows.  

 

The problem is that there are mutiple fields in the entity table that could relate to Systemuser table.  For example,

 

_ownerid_value

_createdby_value

_modifiedby_value

 

all will have lookup values in the system table aginst systemuser ID.  I've tried to edit this in relationships but I get a message telling me that there is 'ambiguity' between the tables. 

 

There are occassions where I need to know who has booked something (created by) as well as who has the record assigned to them (owner) and even who had changed it.

 

I can't work out how to have the look up value (user's name) appear more than once if that makes sense.

 

can anyone help please?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

I believe that one way to solve this would be to download the Systemuser table 3 times. I would name the tables "Owner Users", "Create Users" and "Modify Users" or something similar to tell them apart from one another. Relate each of the columns in the entity table to its corresponding Systemuser table. Then you can use the column from each of the 3 tables in the same visualization. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

I believe that one way to solve this would be to download the Systemuser table 3 times. I would name the tables "Owner Users", "Create Users" and "Modify Users" or something similar to tell them apart from one another. Relate each of the columns in the entity table to its corresponding Systemuser table. Then you can use the column from each of the 3 tables in the same visualization. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks @Greg_Deckler yes sorry it was a daft question, solution was really obvious I should've worked that out myself!

 

Thank you.

Actually I tried this and my Created By field and my Modifed By field using RELATED('Tablename[column]') are blanks.  Owner is still working and yes they are all mapped in the relationship screen

I don't think that you should need any kind of formula but I may not be understanding the issue correctly, I guess what I envisioned was that you had a main table with an numeric ID in your three columns you mentioned. Each of these is related to one of the three System User tables by that ID but they also have the name of the individual. In this case, you should just have to create a table visualization with some column from your main table and then just drop in the "Name" columns from your three related tables and everything *should* work.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




 

I've been going off this link here explaining how to make the queries run quicker http://us.hitachi-solutions.com/blog/2016/06/20/fast-vs-furious-powerbi-dynamics-crm-avoid-common-mi...

 

Anyway, I've re-imported the systemuser table again twice from source and it has data in it now for create by and modified by.  I've put the relationships in and they show up in the visuals.

 

Thanks again.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors