Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Vvelarde
Community Champion
Community Champion

@Anonymous

 

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
Anonymous
Not applicable

@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

Anonymous
Not applicable

@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]))

Your relationship look lie this;?

 

Created and Modified related to User column in user table 

 

AA.png




Lima - Peru
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.