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:
Excel for reference: Sample
Solved! Go to Solution.
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" )
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?
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.