Need Suggestion

[suvechha]

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. 

[v-xicai]

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.

[suvechha]

Hi Amy ,

I tried your suggestion but it didn't work.

[amitchandak]

@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

[AllisonKennedy]

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

[suvechha]

How to upload my sample app over here.

[suvechha]

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.

[AllisonKennedy]

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?

[suvechha]

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

[AllisonKennedy · Accepted 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. 

[suvechha]

Thanks Allison for your help.

[suvechha]

Hi Allison,

I am only getting error at Technician.

Please find the sample app link.

https://1drv.ms/u/s!AhqwbtIVEKQ2an7GOEIb_ql2hys

OR Else

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=e2d204b7-...

Thanks,

Suvechha

[Greg_Deckler]

@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.