Link data from column with data from a row.

[Anonymous]

Hi,

I have a table called: WorkOrders. Each line within this table has a separate Workordernumber. The table contains a column with ContractNumber and a column with UrgencyCode as in the example.
I want to link this to another table called: Urgencytimes. This table has the corresponding rows Contractnumber and Urgencycode.

If this works, I want to link the columns DiscriptionTime and TimeHours from the table Urgencytimes to the Workordernumbers from table Workorders.

Concrete example:

I want to have the Workordernumber 1200-12 linked to the 1 hour from the TimeHours column. So that I can subtract the RealResponsetime: 0.5 and the TimeHours: 1.

Would be nice if someone could help me!

[lbendlin · Accepted solution]

You need to make a couple design decisions here

- either unpivot the workorders table or pivot the UrgencyTimes table to bring them into the same format

- create a composite key ("contractnumber-urgency") that you can then use to join the tables in the data model, or use TREATAS

please confirm if that is acceptable. Attached is a version using TREATAS

[Ashish_Mathur]

Hi,

I do not understand.  Show the expected result clearly.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Hi,

I can't share documents in here?

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link here.  Please show the expected result very clearly.

[Anonymous]

Hi,

Thanks. 

This is the link to the document: 

https://docs.google.com/spreadsheets/d/18E-eitLvOAU-bJSMM6Sr3pcBh4dn9fgY/edit?usp=sharing&ouid=11129... 

Here is the example of the data. The Workorders table and the UrgencyTimes table as mentioned in the top message.

I want to know the difference between, in this example, the RealResponstime(Hours) from the Workorders table and the TimeHours from the Urgencytimes table. I think the Workorders table should be linked to the UrgencyTimes table for this.

For example, in the Workorders table at Workordernumber 1100-14 with Contractnumber 1100, you see Urgencycode HIGH with a RealResponstime(hour) of 1.75 (this is how much time it actually took). In the table UrgencyTimes, you see Contractnumber 1100 and in the same line Urgencycode HIGH, DiscriptionTime Responsetime, TimeHours 2(this is the time in which it has to be done).

I want to compare the RealResponstime(hour) of 1.75 with the TimeHours of 2. This gives a result of 0.25, so I can see that the requirements are met in this case.

I hope this is clear.

[Ashish_Mathur]

Access Denied message.

[Anonymous]

Fixed

[Ashish_Mathur]

Hi,

This calculated column formula in Tabel1 works

=CALCULATE(SUM(Tabel2[TimeHours]),FILTER(Tabel2,Tabel2[Contractnumber]=EARLIER(Tabel1[Contractnumber])&&Tabel2[Urgencycode]=EARLIER(Tabel1[Urgencycode])&&Tabel2[DiscriptionTime]="starttime"))

Hope this helps.

[Anonymous]

Hi,

Thanks for the effort but unfortunately it doesn't work if I do it in Power BI.

It doesn't give an error but I don't get any results out of it. Not even if I put them formula together with the Contractnumber and Workorder in a table.

[Ashish_Mathur]

You are welcome.  It will work very well in PowerBI Desktop.  Please try again.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Hi,

This is the link to the document: 

https://docs.google.com/spreadsheets/d/18E-eitLvOAU-bJSMM6Sr3pcBh4dn9fgY/edit?usp=sharing&ouid=11129... 

Here is the example of the data. The Workorders table and the UrgencyTimes table as mentioned in the top message.

I want to know the difference between, in this example, the RealResponstime(Hours) from the Workorders table and the TimeHours from the Urgencytimes table. I think the Workorders table should be linked to the UrgencyTimes table for this.

For example, in the Workorders table at Workordernumber 1100-14 with Contractnumber 1100, you see Urgencycode HIGH with a RealResponstime(hour) of 1.75 (this is how much time it actually took). In the table UrgencyTimes, you see Contractnumber 1100 and in the same line Urgencycode HIGH, DiscriptionTime Responsetime, TimeHours 2(this is the time in which it has to be done).

I want to compare the RealResponstime(hour) of 1.75 with the TimeHours of 2. This gives a result of 0.25, so I can see that the requirements are met in this case.

I hope this is clear.