Forum Discussion

Anonymous's avatar
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...
Need Help
  • AlB's avatar
    AlB
    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"
    )
Anonymous's avatar
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's avatar
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's avatar
    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

    • AlB's avatar
      AlB
      Community Champion
      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"
    )