Forum Discussion
Need Suggestion
suvechha
You need to define the entire path of relationships and it seems that RELATED() function does not like the USERELATIONSHIP, so I have fixed it. Please check if this returns the expected results for technician:
Technician = CALCULATE(SELECTEDVALUE(AaaUser[FIRST_NAME]), USERELATIONSHIP(WorkOrder[WORKORDERID],WorkOrderStates[WORKORDERID]), USERELATIONSHIP(WorkOrderStates[OWNERID], SDUser[USERID]), USERELATIONSHIP(SDUser[USERID], AaaUser[USER_ID]))UPDATE: suvechha if you are getting an error about single path, you will need to make the relationship between WorkOrder and WorkOrderStates a many to one relationship with single cross filter direction.
You are using the AaaUser table twice in your SQL statement, and relating it to the SAME fact table WorkOrder. This is not possible in Power BI with active relationships, so you have two options.
Option 1: Duplicate AaaUser table in your Power BI data model - name one of them Requester (the current one with the current relationships you have set up). Name the second one Technician and create relationship between TechnicianAaaUser[UserID] and WorkOrderStates[OwnerID]
Option 2: Create an inactive relationship between AaaUser[UserID] and WorkOrderStates[OwnerID] and use DAX to get the values you want when you want.
I am guessing you want to be able to filter for both Tech and Requester, so if you want to have first name, last name, etc all available, then go with Option 1. If you just need FirstName, then go with Option 2 and create Calculated Columns in the Work Order Table for
Requester = RELATED(AaaUser[FirstName])
Technician = CALCULATE(RELATED(AaaUser[FirstName]), USERELATIONSHIP( WorkOrderStates[OwnerID], AaaUser[UserID]))
Hi Allison,
I took the option 2.
But I am getting an error message
The column 'AaaUser[FIRST_NAME]' either doesn't exist or doesn't have a relationship to any table available in the current context.
Please kindly assist.
- You can upload the sample file to OneDrive and share the link.
Are you getting the error message on Requester or Technician or both?
Do all the arrows in the relationships you created make a cascading connection from AaaUser to WorkOrder with all the arrows pointing toward WorkOrder table?Hi Alison ,
Your suggestion Option 1 work.
But I want the Option2 to work.
I followed every steps you mentioned,
Please can you have a look on the sample file and let me know what is wrong.
https://1drv.ms/u/s!AhqwbtIVEKQ2bBzZ7PyqQ6ot-vk?e=coQGzS
You steps helped me to acheive almost what I want but not working 100%.
Thanks
Suvechha
How to upload my sample app over here.
