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,

 

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

 

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.