Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
How to upload my sample app over here.
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks Allison for your help.
Hi Allison,
I am only getting error at Technician.
Please find the sample app link.
https://1drv.ms/u/s!AhqwbtIVEKQ2an7GOEIb_ql2hys
OR Else
Thanks,
Suvechha
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |