Forum Discussion
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 LOOKUPVALUE() function, resulting this:
What I want are like this:
JobIDReq. DeptJob Status
| A01 | Electrical | Done |
| A02 | Mechanical | Done |
| A03 | IT | Continue |
| A04 | Piping | NA |
| A05 | Civil | NA |
Really appreciate for your help,
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- Jihwan_Kim
Super User
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 )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?
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:
