Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Vlookup multiple values and return value if criteria is fullfiled

Hi,

 

I have two tables: Order and Case and I would like the Case table to lookup the OrderStatus in the Order table. They are connected through OrderId. If all OrderId within the CaseId has status 2 or 3 the column should return 1 otherwise 2. Its important that all OrderId either has the status 2 or 3.

 

Ordetstatus: 2 or 3 is = invoiced

Niclasthell_0-1682501668509.png

 

1 ACCEPTED SOLUTION

@Anonymous 

Invoiced =
IF (
    ISEMPTY (
        EXCEPT (
            SELECTCOLUMNS (
                FILTER ( ALL ( Order ), Order[CaseID] = Case[CaseID] ),
                "@OrderStatus", Order[OrderStatus]
            ),
            { 2, 3 }
        )
    ),
    0,
    1
)

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

One-One and Both

Thanks!

@Anonymous 
Apologies for the late response. Please try

Invoiced =
IF (
    1
        IN CALCULATETABLE (
            VALUES ( Order[OrderStatus] ),
            ALLEXCEPT ( Case, Case[CaseID] )
        ),
    0,
    1
)

or

Invoiced =
IF (
    1
        IN SELECTCOLUMNS (
            FILTER ( ALL ( Order ), Order[CaseID] = Case[CaseID] ),
            "@OrderStatus", Order[OrderStatus]
        ),
    0,
    1
)
Anonymous
Not applicable

Thanks again! I managed to get the OrderStatus to the Case table but I´m still struggling on the code. I want to check if all the orders (can be multiple) within the same case has OrderStatus either 2 or 3 then return 1, otherwise 0. All orders need to have either 2 or 3 as OrderStatus to return 1.

Niclasthell_0-1682517565162.png

 

 

 

 

 

 

 

 

 

@Anonymous 

Invoiced =
IF (
    ISEMPTY (
        EXCEPT (
            SELECTCOLUMNS (
                FILTER ( ALL ( Order ), Order[CaseID] = Case[CaseID] ),
                "@OrderStatus", Order[OrderStatus]
            ),
            { 2, 3 }
        )
    ),
    0,
    1
)
Anonymous
Not applicable

Maybe I´m being a little bit inexplicit about the task. But I´´ve merged everything into one table. So right now I want to check if all the orders (can be multiple) within the same case has either OrderStatus 2 or 3 then return 1, otherwise 0. All orders need to have either 2 or 3 as OrderStatus to return 1.

Niclasthell_0-1682520647262.png

 

Thank you @Anonymous 

I have posted two answers and yet you did not specifically indicate what is wrong or what results are you getting. Would you please advise what results are getting out of the 2nd solution? I guess you need to switch position of 1 and 2 inside the IF statement 

Anonymous
Not applicable

Hi, sorry for the late respond.

 

Regarding the 2nd solution the code points towards both tables, but I merged them into one. It worked when I had two separate tables. Thank you! One thing I dont get regarding the code is this:

"@OrderStatus"

Can you explain what it does?

 

Thanks again

@Anonymous 

This just the alias column name argument of the SELECTCOLUMNS function. The @ sign is used as a standard when referencing a temporarily created column to be easily identified 

tamerj1
Super User
Super User

Hi @Anonymous 
What is the direction and the cardinality of the relationship?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.