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.

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

it is for creating a new column.

 

 

Job Status =
VAR lastattempt =
    MAXX ( RELATEDTABLE ( Job_Status ), Job_Status[Attempt] )
VAR result =
    LOOKUPVALUE (
        Job_Status[Job Status],
        Job_Status[JobID], Department[JobID],
        Job_Status[Attempt], lastattempt
    )
RETURN
    IF ( result = BLANK (), "NA", result )
  • prabayuwana's avatar
    prabayuwana
    Regular Visitor
    4 years ago

    Hi, thanks for your help, it working for this set of table,

    but in my real data set, the attemp colomn was a date data type, and it resulting error. is there something i miss?

    • 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
        Icon for Super User rankSuper 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.