cancel
Showing results for
Did you mean:
New Member

## GROUPBY and VLOOKUP (???)

Dear The Community World, seems I can not solve my task without your help!

I have a table and I need to make grouping (firts by Project, then MAX Rev.Date, then MAX Rev.Number) and afterwards return the value from another coloumn.

More clear in the picture bellow:

I didn't find a similar task in our Forum.

Looking forward with your response!

BR Den

1 ACCEPTED SOLUTION
Super User

Hi @DenTOK

Try this:

``````Latest Code =
IF (
HASONEVALUE ( Projects[Project] ),
CALCULATE (
SELECTEDVALUE ( Projects[CODE] ),
GENERATE (
LASTDATE ( Projects[Revision Date] ),
LASTNONBLANK ( Projects[Revision Number], 0 )
)
)
)``````

The key part of the measure is GENERATE ( ... ), which finds the maximum Revision Date using LASTDATE (returning a 1x1 a table), and then finds the maximum Revision Number in the context of that date filter using LASTNONBLANK (also returning a 1x1 table). These become a combined filter, and the single value of CODE is returned with this filter applied.

The measure is also restricted to return blank unless a single Project is visible in the filter context.

Regards,

Owen

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
2 REPLIES 2
Super User

Hi @DenTOK

Try this:

``````Latest Code =
IF (
HASONEVALUE ( Projects[Project] ),
CALCULATE (
SELECTEDVALUE ( Projects[CODE] ),
GENERATE (
LASTDATE ( Projects[Revision Date] ),
LASTNONBLANK ( Projects[Revision Number], 0 )
)
)
)``````

The key part of the measure is GENERATE ( ... ), which finds the maximum Revision Date using LASTDATE (returning a 1x1 a table), and then finds the maximum Revision Number in the context of that date filter using LASTNONBLANK (also returning a 1x1 table). These become a combined filter, and the single value of CODE is returned with this filter applied.

The measure is also restricted to return blank unless a single Project is visible in the filter context.

Regards,

Owen

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
New Member

THANK YOU, @OwenAuger !

Your proposal works as it should!

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors