Forum Discussion
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...
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
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.
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
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" )