Lookup with Filter

[AllanBerces]

Hi Good day,

Can anyone assist me on my code, base from my current code i want to add filter on a specific column.

My current code:

I want to add filter on coloumn:

Table: BSP TA
Column 1: Project Name = ABCDE

Column 2: Main Cat. = Q

Base_Scope =

VAR lookupResult = LOOKUPVALUE(sign_off[sign off], sign_off[Job Card], 'BSP TA'[Job card])

RETURN IF(ISBLANK(lookupResult), "Injected", lookupResult)

Thank you

[v-yajiewan-msft]

Hi @AllanBerces ,

As I understand it, you want to filter the table based on two column values and then use the LOOKUPVALUE later.

Please try as followings:

Base_Scope =
VAR ProjectName =MAX('BSP TA'[Project Name])
VAR MainCat = MAX('BSP TA'[Main Cat.])
VAR JobCard = MAX('BSP TA'[Job card])
RETURN
    IF(
        ProjectName = "ABCDE" && MainCat = "Q",
        IF(
            ISBLANK(
                LOOKUPVALUE(
                  sign_off[sign off],
                    sign_off[Job Card],
                    JobCard
                )
            ),
            "Injected",
            LOOKUPVALUE(
                sign_off[sign off],
                sign_off[Job Card],
                JobCard
            )
        ),
        BLANK()
    )

Sample data from the sign-off table:

Before:

After:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best regards,

Joyce

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

[AllanBerces]

Hi @v-yajiewan-msft , @Ashish_Mathur 

Good day,

I try the solution above but show blank. Can pls help me to solve my issue. Pls refer to the link.

https://drive.google.com/file/d/1NV1BiF6qCEq2L2HPMhYwim1Xxxfm1lCc/view?usp=sharing

https://drive.google.com/file/d/1NV1BiF6qCEq2L2HPMhYwim1Xxxfm1lCc/view?usp=sharing

Thank you

[Ashish_Mathur]

Hi,

Try this

Project Name = LOOKUPVALUE('Main Data'[Project Name], 'Main Data'[Job card], 'Sign off'[Job Card] ,'Main Data'[Main Cat.],"Q",'Main Data'[Project Name],"ABCDE")

This will return a blank because there is no ABCDE entry in the Project name column.

[AllanBerces]

Hi @Ashish_Mathur showing error on this part

Thank you

[Samarth_18]

Hi @AllanBerces ,

You could try the following code:-

Project Name =
LOOKUPVALUE(
    'Main Data'[Project Name],
    'Main Data'[Job card], RELATED('Sign off'[Job Card]),
    'Main Data'[Main Cat.], "Q",
    'Main Data'[Project Name], "ABCDE"
)

[AllanBerces · Accepted solution]

Hi @Samarth_18 thank you for the help, its working now.

[AllanBerces]

Hi follow up to my query 

I want first to filter the table before it do lookup.

Table: BSP TA
Column 1: Project Name = ABCDE

Column 2: Main Cat. = Q

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.