Forum Discussion
if condition between two tables
Hello Everyone,
I am working on Time and Motion data which requires excluding the line items which are not aligned to Project TAT.
So I have a table which has data for day to day line items which were completed by the team members in the process with Start and End time and duration to complete(in seconds)
I have another table which consists of the Process name, size of work, and Min and Max seconds to complete the particular process.
Please see the below table for more understanding:
| Table A | Table B | ||||||
| Action name | SIZE | Time taken to process(Sec.) | Action name | SIZE | Min Sec | Max Sec | |
| ABC Process | 0-200 | 480 | ABC Process | 0-200 | 250 | 750 | |
| GF Process | 0-200 | 2323 | ABC Process | >200 | 750 | 2200 | |
| UI Process | >200 | 3434 | GF Process | 0-200 | 250 | 750 | |
| HGF Process | >200 | 1290 | GF Process | >200 | 750 | 2200 | |
| ABC Process | 0-200 | 576 | UI Process | 0-200 | 250 | 750 | |
| GF Process | 0-200 | 243 | UI Process | >200 | 750 | 2200 | |
| ABC Process | >200 | 2353 | HGF Process | 0-200 | 250 | 750 | |
| UI Process | 0-200 | 354 | HGF Process | >200 | 750 | 2200 | |
| ABC Process | >200 | 1980 |
As we have duplicates in both the tables, I have created a bridge table and connected both the tables.
For example, ABC process as 4 lines of work in table A and if compare the process and size with Table B, line # 7 (highlighted in red)does not qualify and excluded from the analysis and replace with Blank.
Please give me some advice, on how to achieve this.
Thanks
Switto - You will want to use RELATED or RELATEDTABLE or possibly LOOKUPVALUE and could also use MAXX(FILTER(...)...)
6 Replies
HI Switto
Can you please share your sample pbix file if you can. It would bring more clarity to your issue.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904- Thanks Ankit for the note, however it has confidential data hence unable to share it. Appreciate you quick reply. Thanks
Switto
For such multiple tables senario, you can create a short sample with random data to describe your model, you are unlikely to get an expected solution without a sample pbix.Regards
Paul
Switto - You will want to use RELATED or RELATEDTABLE or possibly LOOKUPVALUE and could also use MAXX(FILTER(...)...)
Thanks for the tips 🙂 Let me read up and try to put into solution.
Appreciate your quick reply.
Greg_Deckler : Thanks for the tip. I have used the "lookupvalue" to get the data from my AHT file to Rawfile.
It worked for taking the minimum time however, while using it for Maximum time, got the error "A circular dependency was detected: RawFile[mMax_Time], RawFile[mMini_Time], RawFile[mMax_Time]."
I have used formula for mini. as
LOOKUPVALUE(AHT[mMin_AHT_Sec],AHT[Process Name],RawFile[Action name],AHT[Size],RawFile[mTree_size])and, Max isLOOKUPVALUE(AHT[mMax_AHT_Sec],AHT[Process Name],RawFile[Action name],AHT[Size],RawFile[mTree_size])I am not sure why I am getting this.Any idea will help.
