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.

BR Den

1 ACCEPTED SOLUTION
Super User I

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

My Blog
2 REPLIES 2
Super User I

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

My Blog
New Member

THANK YOU, @OwenAuger !

Your proposal works as it should!

Announcements

#### Welcome to the User Group Public Preview

Check out how to claim yours today!

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors