Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DenTOK
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:

DenTOK_0-1617544798221.png

 

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

Looking forward with your response!

BR Den

1 ACCEPTED SOLUTION
OwenAuger
Super User
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
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
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
Twitter
LinkedIn

THANK YOU, @OwenAuger !

Your proposal works as it should!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.