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
Thanks!
Solved! Go to 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" )
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" )
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
@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.
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" )
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
212 | |
51 | |
45 | |
41 | |
39 |
User | Count |
---|---|
277 | |
210 | |
73 | |
72 | |
65 |