topic Re: Vlookup multiple values and return value if criteria is fullfiled in DAX Commands and Tips

Vlookup multiple values and return value if criteria is fullfiled

Anonymous — Wed, 26 Apr 2023 09:35:39 GMT

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

Re: Vlookup multiple values and return value if criteria is fullfiled

tamerj1 — Wed, 26 Apr 2023 09:54:15 GMT

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

Re: Vlookup multiple values and return value if criteria is fullfiled

Anonymous — Wed, 26 Apr 2023 10:11:17 GMT

One-One and Both

Thanks!

Re: Vlookup multiple values and return value if criteria is fullfiled

tamerj1 — Wed, 26 Apr 2023 12:53:35 GMT

Anonymous
Apologies for the late response. Please try

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

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

Re: Vlookup multiple values and return value if criteria is fullfiled

Anonymous — Wed, 26 Apr 2023 13:59:32 GMT

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.

Re: Vlookup multiple values and return value if criteria is fullfiled

tamerj1 — Wed, 26 Apr 2023 14:15:26 GMT

Anonymous

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

Re: Vlookup multiple values and return value if criteria is fullfiled

Anonymous — Wed, 26 Apr 2023 14:51:52 GMT

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.

Re: Vlookup multiple values and return value if criteria is fullfiled

tamerj1 — Fri, 28 Apr 2023 08:35:10 GMT

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

Re: Vlookup multiple values and return value if criteria is fullfiled

Anonymous — Fri, 28 Apr 2023 07:33:50 GMT

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

Re: Vlookup multiple values and return value if criteria is fullfiled

tamerj1 — Fri, 28 Apr 2023 08:41:33 GMT

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