Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Lookup with switch case

Table 1: consisting of Jobs:
pbi_community_job.png

 

Table 2:  Invoices of certain jobs in the job table. Not all the jobs have an invoice.

 

pbi_community_invoice.png

 

 

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!

 

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

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

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
Not applicable

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.

Anonymous
Not applicable

@AlB

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.