Lookup with switch case

[Anonymous]

Table 1: consisting of Jobs:

Table 2:  Invoices of certain jobs in the job table. Not all the jobs have an invoice.

How do I calculate the Status column in the Job table using DAX?

Logic for status:

• If flag=1 for job id in invoice, then Status="Current"
• If flag=0 for job id in invoice, then Status="Old"
• If job id in jobs is not there in Invoice, the "NG"

Excel for reference: Sample

Thanks!

[AlB]

Hi @Anonymous

Try this for your Status column in Jobs:

Status =
VAR _Flag =
    LOOKUPVALUE (
        Invoices[Flag],
        Invoices[Job ID], Jobs[Job ID]
    )
RETURN
    SWITCH (
        _Flag,
        BLANK (), "NG",
        0, "Old",
        1, "Current"
    )

[Anonymous]

Hi @AlB,

I was not aware about the Return function, Thanks!

One small issue, the job ids in the invoice table aren't unique. Hence while looking up, i get the error: "A table of multiple values was supplied where a single value was expected."

Based on the resolution Here, I tried doing it like:

VAR _Flag = CALCULATE (
    FIRSTNONBLANK ( Invoice[Flag], 1 ),
    FILTER ( ALL ( Invoice ), Invoice[Job ID] = Job[Job Id] )
)
   

But some rows are getting misclassified here.

Any other way to handle this?

Thanks

[AlB]

@Anonymous

Well, first we'll need to clarify what you want to do when there are several different flags for a Job ID. Which one do you want to select in that case? That should have been stated in the opening question.

[Anonymous]

@AlB

Yeah should've mentioned earlier, sorry about that.

There is a 1:* relation between job and invoice tables i.e. one job can have multiple invoices but not the other way around. 

• If even a single occurrence of a job id in Invoice table has flag = 1, then respective job id in job table should have Status="Current".
• If all the occurrences of a  job id in Invoice table has flag = 0, then respective job id in job table should have Status="Old".
• If job id in jobs is not there in Invoice, the "NG"

That's it

[AlB · Accepted solution]

@Anonymous

Status =
VAR _Flag =
    CALCULATE (
        MAX(Invoices[Flag]),
        FILTER(ALL(Invoices[Job ID]),Invoices[Job ID] = Jobs[Job ID])
    )
RETURN
    SWITCH (
        _Flag,
        BLANK (), "NG",
        0, "Old",
        1, "Current"
    )