Forum Discussion
Creating an Exclusion Indicator Column Based on Combination Values from an Exclusion Table
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:
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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
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.
