Forum Discussion

prabayuwana's avatar
prabayuwana
Regular Visitor
4 years ago
Solved

Select 1 Lookup Value from Multiple Result

Need help, i want to create tracking job status by spesific job ID on table bellow: Table 1. Department   From another log job table bellow: Tabel 2. Job Status   But when I use LOO...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi prabayuwana ,

     

    LOOKUPVALUE function will return value based on the filter, however in your sample, group by JobID and max Attemp Date, we will get multiple results. For example: A01, 2022/01/04 will return Continue and Done. So you will get error.

    I have a test by your sample.

    You can try this code.

    Job Status = 
VAR _lastattempt =
    MAXX ( RELATEDTABLE ( 'Job Status' ), 'Job Status'[Attemp] )
VAR _RESULT =
    CALCULATETABLE (
        VALUES ( 'Job Status'[Job Status] ),
        FILTER ( RELATEDTABLE ( 'Job Status' ), 'Job Status'[Attemp] = _lastattempt )
    )
RETURN
    IF (
        _lastattempt = BLANK (),
        "NA",
        IF ( "Done" IN _RESULT, "Done", "Continue" )
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

prabayuwana's avatar
prabayuwana
Regular Visitor
4 years ago

Hi Jihwan_Kim, bellow is massage of the error if I simulate the same attemp number (wich could be happen if the record is a date dataset).

 

This happen when I change attemp colomn on job status as bellow:

 

 

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please share your sample pbix file, and then I can try to come up with a more accurate solution.

Thank you.

  • prabayuwana's avatar
    prabayuwana
    Regular Visitor
    4 years ago

    Hi Jihwan_Kim,

     

    I cant attach file. there is limitation for regular member. 

    "Regular community members with no additional roles or permissions can not upload files to their posts. We recognize that this might be something we need to address, and we certainly will be discussing this, however we don't have a timeline when this will take place"

     

    But you can simulate with your previouse file, just change the attemp colomn as bellow, it will help me:

     

    JobIDExcecutorAttempJob Status
    A01Budi1 Jan 2022Continue
    A01Andi4 Jan 2022Continue
    A01Budi4 Jan 2022Done
    A02Riko3 Jan 2022Continue
    A02Niko5 Jan 2022Done
    A03Joni4 Jan 2022Continue

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      Hi,

      Thank you for your message.

      If it is OK with you, you can share link ( OneDrive, GoogleDrive, DropBox, or others) to share your sample file.

      By the way, JobID A01 => which one of two 4th Jan 2022s is the last attempt? Is "Done" always the last? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi prabayuwana ,

       

      LOOKUPVALUE function will return value based on the filter, however in your sample, group by JobID and max Attemp Date, we will get multiple results. For example: A01, 2022/01/04 will return Continue and Done. So you will get error.

      I have a test by your sample.

      You can try this code.

      Job Status = 
VAR _lastattempt =
    MAXX ( RELATEDTABLE ( 'Job Status' ), 'Job Status'[Attemp] )
VAR _RESULT =
    CALCULATETABLE (
        VALUES ( 'Job Status'[Job Status] ),
        FILTER ( RELATEDTABLE ( 'Job Status' ), 'Job Status'[Attemp] = _lastattempt )
    )
RETURN
    IF (
        _lastattempt = BLANK (),
        "NA",
        IF ( "Done" IN _RESULT, "Done", "Continue" )
    )

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.