Creating an Exclusion Indicator Column Based on Combination Values from an Exclusion Table

[PBI_newuser]

Hi, I want to create a column that indicates exclusion based on the combination of values in the exclusion table (Sheet1). The exclusion table contains various combinations of field values, such as LOADING_GROUP_CD = "S108" and MATERIAL_GROUP_CD = "0015", which need to be excluded from the dataset.

However, with the current column I created, I can't filter out blank values directly. This results in MATERIAL_GROUP_CD = "0015" being excluded for other LOADING_GROUP_CD values as well. How can I modify this column to handle this scenario correctly?
Sample file 

Lookupvalue =
Var confirm_name = LOOKUPVALUE(Sheet1[CONFIRM_NAME],Sheet1[ACITIVITY],TABLE1[ACTIVITY],Sheet1[WAREHOUSE_NO],TABLE1[WAREHOUSE_NO],Sheet1[CONFIRM_NAME],TABLE1[CONFIRM_NAME])
Var loading_group =LOOKUPVALUE(Sheet1[LOADING_GROUP_CD],Sheet1[ACITIVITY],TABLE1[ACTIVITY],Sheet1[WAREHOUSE_NO],TABLE1[WAREHOUSE_NO],Sheet1[LOADING_GROUP_CD],TABLE1[LOADING_GRP_CD])
Var material_group =LOOKUPVALUE(Sheet1[MATERIAL_GROUP_CD],Sheet1[ACITIVITY],TABLE1[ACTIVITY],Sheet1[WAREHOUSE_NO],TABLE1[WAREHOUSE_NO],Sheet1[MATERIAL_GROUP_CD],TABLE1[MATERIAL_GRP_CD])

RETURN
confirm_name & loading_group & material_group

Exclusion Table (Sheet1):

Lookupvalue column created:

[Anonymous · Accepted solution]

Hi @PBI_newuser ，
Unfortunately I can't open your pbix file due to environment constraints, happily I created the instance dataset based on your image and when I was using the Lookvalue function it didn't handle the context well either, so a different way of thinking will achieve what you need, hope my method is helpful!

ExcludeFlag = 
VAR KeyTable1 =
   TABLE1[ACTIVITY] & "_" &
   TABLE1[WAREHOUSE_NO] & "_" &
   TABLE1[CONFIRM_NAME] & "_" &
   TABLE1[LOADING_GRP_CD] & "_" &
   TABLE1[MATERIAL_GRP_CD]
VAR MatchFound =
   LOOKUPVALUE(
       Sheet1[CONFIRM_NAME],
       Sheet1[ACTIVITY], TABLE1[ACTIVITY],
       Sheet1[WAREHOUSE_NO], TABLE1[WAREHOUSE_NO],
       Sheet1[CONFIRM_NAME], TABLE1[CONFIRM_NAME],
       Sheet1[LOADING_GRP_CD], TABLE1[LOADING_GRP_CD],
       Sheet1[MATERIAL_GRP_CD], TABLE1[MATERIAL_GRP_CD]
   )
RETURN
IF(
   NOT(ISBLANK(MatchFound)),
   "Exclude",
   "Include"
)

I would be honoured if my solution could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @PBI_newuser 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[PBI_newuser]

Hi @Ritaf1983 , here is the sample file. 

How can I modify this column to ensure only the exact combinations present in the exclusion table are excluded? For example, the rows highlighted in yellow should be included.