if condition between two tables

[Switto]

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

[Greg_Deckler · Accepted solution]

@Switto - You will want to use RELATED or RELATEDTABLE or possibly LOOKUPVALUE and could also use MAXX(FILTER(...)...)

[Switto]

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

LOOKUPVALUE(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.

[Switto]

Thanks for the tips 🙂 Let me read up and try to put into solution.

Appreciate your quick reply.

[AnkitKukreja]

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

[Switto]

Thanks Ankit for the note, however it has confidential data hence unable to share it. Appreciate you quick reply. Thanks

[V-pazhen-msft]

@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