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 using DAX?

 

Logic for status:

  • If flag=1 for job id in invoice, then Status="Current"
  • If flag=0 for job id in invoice, then Status="Old"
  • If job id in jobs is not there in Invoice, the "NG"

Excel for reference: Sample

 

Thanks!

 

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"
    )

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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"
    )

     

    • 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.