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

Creating relationships to multiple columns and displaying data

I have imported a SharePoint list IncidentTracker. The "Created By" and "Modified By" columns are displayed as "CreatedByID" and "ModifiedByID" respectively.

 

I also imported UserInformationList from my SharePoint using the http://mysite/_vti_bin/listdata.svc with OData method.

 

Now I am trying to create a relation ship between IncidentTracker and UserInformationList to get the actual "Created By" and "Modified By", but seem to have been able to connect either to one of the columns.

 

How do I create a relationship so that I can display both Created By and Modified By in my output table?

1 ACCEPTED SOLUTION

 

 

CreatedByUser = CALCULATE(VALUES(User[Name]),USERELATIONSHIP(Incident[Createdby],User[User]))

ModifiedByUser = CALCULATE(VALUES(User[Name]),USERELATIONSHIP(Incident[Modifiedby],User[User]))

 




Lima - Peru

View solution in original post

10 REPLIES 10
Community Champion
Community Champion

@shanka

 

With USERELATIONSHIP in a Dax Measure you can activate relationship between the tables (The active and Inactive)




Lima - Peru

Hey I have a quick question which relates to this.

 

I have a similar situation as listed and implemented the same DAX expressions. When I try to place the measure on my table it's not letting me. It says a table of multiple values was supplied where a single value was expected.

 

Could you please help with this. I can give you all the details of my report as we work on it.

 

 

CreatedByUser = CALCULATE(VALUES(User[Name]),USERELATIONSHIP(Incident[Createdby],User[User]))

ModifiedByUser = CALCULATE(VALUES(User[Name]),USERELATIONSHIP(Incident[Modifiedby],User[User]))

 




Lima - Peru

View solution in original post

@Vvelarde 

If i want to use two more relation ships with the userealtionship function can it be possibel 

 

Calculate(count(inc),userelationship(inc[country],user[country]),userelationship(inc[user],user[user])

 

Thanks in advance

@Vvelarde

 

I tried using the method you mentioned. But I get this error: USERELATIONSHIP function can only use the two columns references participating in relationship

 

CreatedByM = CALCULATE(VALUES(UserInformationList[WorkEmail]),USERELATIONSHIP(IncidentTracker[CreatedById],UserInformationList[Id]))

Community Champion
Community Champion

Your relationship look lie this;?

 

Created and Modified related to User column in user table 

 

AA.png




Lima - Peru

Ok. I got it now! Thanks @Vvelarde 🙂

Hello I am also facing this error. I am really new to powerBI. Can you please help me with this error.

Finally found the solution from this page. Had to use FIRSTNONBLANK instead of VALUES. Otherwise, what was described in this thread worked for me 🙂   

Hi. 

 

I am new to power bi. I'm not sure if I have the same issue. I'm trying to connect create a relationships from 2 reports between a target vs. the actual. I am not getting any error but the results shows the sum of all target.

 

Here is my dax formula: 

 

Target =
CALCULATE(
SUM(Target),
USERELATIONSHIP('Region[UserName],'Actual'[Username]
))
 
Maybe I am doing something wrong. 
 
Hoping for your assistance. 
 
Thank you. 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors