Forum Discussion
Solved
Need Suggestion
Hi there , I have a query like below and I am struggling to get the "ti"."FIRST_NAME" AS "Technician" in my power bi app , Please kindly assist me , as I am new to power BI - it would be good if ...
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.
suvechha
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]))
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]))
How to upload my sample app over here.