Help Needed Please! Tried using Lookup But Error "a table of multiple values was supplied

[Anonymous]

Hi,

Need a help to solve below conditions. 

When I tried using Lookup Value, error was ccoming like "a table of multiple values was supplied where a single value was expected". Please help.

Below is the Data and I was trying to get the Manager Territory and  True / False as shown in the below Table:

Id	Name	Territory Id	Parent Territory Id	Manager ID	Manager Name	Manager Territory (Calculated Field)	True / False (Calculated Field)
1	A	A001	A005	5	EE		TRUE
2	B	A002	A0051	6	FF		TRUE
3	C	A003	A005	7	GG		FALSE
3	C	A004	A007	7	GG		TRUE
4	D	A003	A009	9	II		FALSE
5	EE	A005	A0099	100	JJJ
6	FF	A0051	A0055	55	EEE
7	GG	A006	A0055	66	FFF
7	GG	A007	A0088	66	FFF
9	II	A008	A0099	100	JJJ
10	JJ	A009	A0055	55	EEE

Requirements are:

1. Manager Territory should get populated by Looking the Manager ID in Column "ID" and get the respective Territory ID from Column "Territory Id"

2. Now the Calculated Column "Manager ID" should be matched with column "Parent Territory Id" to flag the true or false. We are doing this since there is a possibility that the Parent Territory ID is wrong mapped for ID. 

For example: ID = 3, his Manage is 7 and Parent Territory id is A005. But the correct parent territory id is A006.

Note: If we are not able to get the Manager ID column, we can ignore it. But we need the Flagging column, whether it is True or False.

Challenges:

One ID (can be manager as well) can have 2 or more territories. So it should be true if the Parent Territory ID falls in any of the Manager ID of the respective Manager. 

For Example: ID 7, he have two territories A006 and A007

Thanks a lot in advance.

[AlexisOlson]

How about this?

IsOK =
IF (
    Data[Manager ID] IN VALUES ( Data[Id] ),
    CONTAINS (
        FILTER ( Data, Data[Id] = EARLIER ( Data[Manager ID] ) ),
        Data[Territory Id], Data[Parent Territory Id]
    ),
    TRUE ()
)

You can list the Manager Territories by using CONCATENATEX instead of CONTAINS.

[Greg_Deckler]

@Anonymous So the problem that I see in this is that I am not sure how to code something that removes the ambiguity around who is the correct manager. For example, for the Parent Territory Id of A005 the data says that the Manager ID is 5, 6 and 7. The 5 and 6 are correct but the seven is incorrect. But, you know that because you are a human that has this business knowledge. However, what is there to tell the code that this is the case?

[Anonymous]

Hi Greg,

Thanks for the reply. 

Sorry for creating confusion. 

Territory ID of 5 is A005, 6 is A0051 and 7 is A006. The updated data is provided below.

So here Parent ID is as per what is there in the System for a particular ID. So there is a posibility the same is tagged wrongly. 

For Example: In ID 3, Manager is ID 7, so the Parent territory should have been A006 (which is the Territory ID of ID 7). But in system it is wrongly tagged as A005, which is the Territory ID of ID 5.  So here a flag should come as False.

Trust I didn't confuse you more. Could you please check the below updated Data?

Id	Name	Territory Id	Parent Territory Id	Manager ID	Manager Name	Manager Territory (Calculated Field)	True / False (Calculated Field)
1	A	A001	A005	5	EE		TRUE
2	B	A002	A0051	6	FF		TRUE
3	C	A003	A005	7	GG		FALSE
3	C	A004	A007	7	GG		TRUE
4	D	A003	A009	9	II		FALSE
5	EE	A005	A0099	100	JJJ
6	FF	A0051	A0055	55	EEE
7	GG	A006	A0055	66	FFF
7	GG	A007	A0088	66	FFF
9	II	A008	A0099	100	JJJ
10	JJ	A009	A0055	55	EEE

[Anonymous]

@VahidDM @Greg_Deckler @amitchandak @AlexisOlson @Jihwan_Kim 

Trust you are well.

Could you please help on the above?