Vlookup multiple values and return value if criteria is fullfiled

[Anonymous]

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

[Anonymous]

One-One and Both

Thanks!

[tamerj1]

@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]

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.

[tamerj1 · Accepted solution]

@Anonymous 

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

[Anonymous]

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.

[tamerj1]

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]

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

[tamerj1]

@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]

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