Forum Discussion

suvechha's avatar
suvechha
Helper IV
6 years ago
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 ...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    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. 

v-xicai's avatar
v-xicai
Community Support
6 years ago

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's avatar
    suvechha
    Helper IV
    6 years ago

    Hi Amy ,

     

    I tried your suggestion but it didn't work.