Forum Discussion
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 any one suggest any thing in details.
SELECT
"ti"."FIRST_NAME" AS "Technician",
"wo"."WORKORDERID" AS "Request ID",
"aau"."FIRST_NAME" AS "Requester",
"wo"."CREATEDTIME" AS "Created Time"
FROM "WorkOrder" "wo"
LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID"
LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID"
LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID"
WHERE
(
CONVERT(VARCHAR,DATEADD(SECOND, "wo"."CREATEDTIME"/1000 ,'19700101'),111) >='2020/08/01' and
CONVERT(VARCHAR,DATEADD(SECOND, "wo"."CREATEDTIME"/1000 ,'19700101'),111) <='2020/08/18'
)
My power bi app data model is attached.
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.
12 Replies
- 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]))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?
How to upload my sample app over here.
suvechha - Not great with the SQL, that is renaming the column within the query, correct? I don't know of way to do that other than to use SELECTCOLUMNS.
If that is not it, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.suvechha , In power BI you can rename the column at table level or at the visual level. Is rename is a challenge or join are not working as expected
Hi suvechha ,
The relationships among these tables are equal to the join part in your SQL codes, so there is no need to add these join parts into your DAX formula. While you may change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table.
What's more, the "ti"."FIRST_NAME" and the "aau"."FIRST_NAME" are referred to the same table 'AaaUser', so you may create two calculated columns in the table 'WorkOrder' like DAX below.
Requester = RELATED(AaaUser[FirstName]) Technician = RELATED(AaaUser[FirstName])For the part "wo"."WORKORDERID" AS "Request ID" and "wo"."CREATEDTIME" AS "Created Time", you may go to table 'WorkOrder' in Field pane directly, rename the [WORKORDERID] with [Request ID], rename the [CREATEDTIM] with [Created Time] manually.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy ,
I tried your suggestion but it didn't work.
