Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
2 years ago
Solved

Lookup with Filter

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

 

Need Help

8 Replies

  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    2 years ago

    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's avatar
      Ashish_Mathur
      Super User
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.