use of THISRECORD within LOOKUP to reference FORALL record above

[SIDOU]

Hello, 

Please I have a Table Named Test2 which contains the following columns: 'Train-ID', 'TRK', 'Location'.

for any Train-ID there are two rows (one for Location1, and second for Location2).

I want to filter Test2 to get only Train-ID Which stratified the following condition:

at Location1 the TRK="1" And at Location2 the TRK="3".

I tried to use the following code but it does not work:

FORALL(
Filter(
Test2,
Location = "Location1" And TRK="1" ),
If( !ISBLANK(LOOKUP(
Test2,
Location="Location2" And TRAIN=THISRECORD.TRAIN , TRK)="3"),

Collect(Movement, THISRECORD))
)
)

I'm a new user to power apps and coding, so please give me a detailed explanation which will be a learning opportunity for me.

Thank you in advance for your help

[SpongYe]

Hi @SIDOU 

First of all, as a new user you came pretty far with this. 

So in the first part of the the Forall() we filter the data Location = "Location1" And TRK="1".

In the second part we CountRows the records found in the LookUp. We convert the output of the LookUp from Text to Value and then if it is 3 then we Collect the ThisItem.

FORALL(
    Filter(
        Test2,
        Location = "Location1" And TRK="1" 
    ),
    If( 
        CountRows(
            Value(LOOKUP(Test2,Location="Location2" And TRAIN=THISRECORD.TRAIN , TRK))
        ) = 3,
        Collect(Movement, THISRECORD)
    )
)

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[BCBuizer]

Hi @SIDOU ,

Whilst in theory this should work, I run into issues all the time, so I use the As operator to override ThisRecord (https://learn.microsoft.com/en-us/power-platform/power-fx/reference/operators) :

ForAll(
	Filter(
		Test2,
		Location = "Location1" && TRK = "1" 
	) As _Records,
	If( 
		!IsBlank(
			LookUp(
				Test2,
				Location = "Location2" && TRAIN= _Records.TRAIN ,
				TRK
			) = "3" 
		),
		Collect(Movement, _Records))
	)
)

A thing I notice though is that you have something weird going on in the conditions for your If formula where first you perform a LookUp, then check if the TRK column of the returned record equals "3".... and then checks if the result of that condition !IsBlank...

[SIDOU]

Hi, Actually I do not want count the rows, what I want is that to take from the Filte(Test2...

only the records which satisfy the condition of the If() below. my table has the Train_ID and for each Train_ID there are two locations "Location1" and "Location2", which means for 

I

Train_ID	Location	TRK
train1	location1	1
train1	location2	1
train2	location1	1
train2	location2	3
train3	location1	2

I want my code to retrieve only train2 rows because it is the only one which satisfied the condition: at location1 the TRK=1 and at location2 the TRK=3

thank you

[SIDOU]

actualy I tried the As but it did not work. below is a detailed of what I want:

 what I want is that to take from the Filte(Test2...

only the records which satisfy the condition of the If() below. my table has the Train_ID and for each Train_ID there are two locations "Location1" and "Location2", which means for 

I

Train_ID	Location	TRK
train1	location1	1
train1	location2	1
train2	location1	1
train2	location2	3
train3	location1	2

I want my code to retrieve only train2 rows because it is the only one which satisfied the condition: at location1 the TRK=1 and at location2 the TRK=3

thank you

[BCBuizer]

Hi @SIDOU ,

Please try this then:

ForAll(
	Filter(
		Test2,
		Location = "Location1" && TRK = "1" 
	) As _Records,
	If( 
		!IsBlank(
			LookUp(
				Test2,
				Location = "Location2" && TRAIN= _Records.TRAIN && TRK = "3"
			) 
		),
		Collect(Movement, _Records))
	)
)

I made a change so TRK = "3" is now a condition for the LookUp instead of the If function.

[SIDOU]

HOOW, THANK YOU IT IS WORKING.

please, how can I also collect the record found in the lookup function to the collection Movement

[BCBuizer · Accepted solution]

Hi @SIDOU ,

To add the return of the LookUp function to the collection you can chain a second Collect function after the first that contains the same Lookup, or you can use a With function to evaluate the LookUp once and then use the result both in the If function and the second Collect function:

Clear(Movement);

ForAll(
	Filter(
		Test2,
		Location = "Location1" && TRK = "1" 
	) As _Records1,
	With({
		_Records2: LookUp(
			Test2,
			Location = "Location2" && TRAIN= _Records1.TRAIN && TRK = "3"
		)},
		If( 
			!IsBlank(_Records2),
			Collect(Movement, _Records1);
			Collect(Movement, _Records2)
		)
	)
)

[SIDOU]

THANK YOU VERY MUCH, YOU'RE A CHAMPION