Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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" )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
110 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |