Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

Lookup with switch case

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 usi...

7 years ago

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"
    )

Community Champion

7 years ago

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
Not applicable
7 years ago
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
  Community Champion
  7 years ago
  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
    Not applicable
    7 years ago
    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

Related content