Forum Discussion
Lookupvalue Problem
I think I understand what you are trying to do but it seems like your LOOKUPVALUE criteria is not specific enough to return a single value but rather multiple values. I suspect that you must have multiple lines in that other table, perhaps per status with a date assigned. If that is the case, you might try something like this instead:
Column = VAR __table = FILTER(ALL('Table2'),[Column] = [ID]) VAR __max = MAXX(__table,[Date]) VAR __status = MAXX(FILTER(__table,[Date]=__max),[LookupColumn]) RETURN __statusSo, take the [ID] from your original table and FILTER ALL of the table to a matching column, now you have all rows in the 2nd table for that [ID]. Then, find the MAX date in that table of filtered rows. Finally, filter down to that exact date in the filtered row table and return the status that you are trying to grab ([LookupColumn]).
Something along those lines.
Community ChampionYou might try wrapping that in a CONCATENATEX to concatenate multiple values together.
Greg, thanks for responding!
I think that is where I am getting confused. I know my thinking is wrong, but I'm currently thinking that for each row in my cases table I should be able to pull the unique Product stage value for that company/product.
My up to would be to highlight cases that the product was in a Training stage for example.
Each Product state would show up in the row next to all the other case details and I would do some conditional formatting to highlight those cases.
Something like this...
Not sure if any of that made sense. But I appreciate the help.
- Greg_Deckler
I think I understand what you are trying to do but it seems like your LOOKUPVALUE criteria is not specific enough to return a single value but rather multiple values. I suspect that you must have multiple lines in that other table, perhaps per status with a date assigned. If that is the case, you might try something like this instead:
Column = VAR __table = FILTER(ALL('Table2'),[Column] = [ID]) VAR __max = MAXX(__table,[Date]) VAR __status = MAXX(FILTER(__table,[Date]=__max),[LookupColumn]) RETURN __statusSo, take the [ID] from your original table and FILTER ALL of the table to a matching column, now you have all rows in the 2nd table for that [ID]. Then, find the MAX date in that table of filtered rows. Finally, filter down to that exact date in the filtered row table and return the status that you are trying to grab ([LookupColumn]).
Something along those lines.
Thanks again Greg! You were right. The data table I expected to be unique had duplicates. The original LookupValue statement worked right after you pointed me in that direction.
I appreciate the help.
- Greg_Deckler
Awesome Anonymous - glad you got it!!