Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
suvechha
Helper IV
Helper IV

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_0-1597970852216.png

 

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


Please @mention me in your reply if you want a response.

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

View solution in original post

12 REPLIES 12
v-xicai
Community Support
Community Support

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.

173.png

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.

 

 

175.png

 

 

 

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.

amitchandak
Super User
Super User

@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
Super User
Super User

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


Please @mention me in your reply if you want a response.

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.

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?

Please @mention me in your reply if you want a response.

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. 


Please @mention me in your reply if you want a response.

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

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

Thanks,

Suvechha

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.