Create collection grouping by Lookup column

[drstrong]

My overall objective is to create a collection from my Notes table that returns the corresponding note detail value for the max created record for that given lookup value.

note table structure 

ID  LookUpValue  Note Detail  Created On

1    1111               note 1.          3/1/24

2    1111               Note 2.          3/9/24

3    2222                Note 3.          3/4/24

4    3333               Note 4.          3/3/24

5   2222               Note 5.           3/5/24

expected results

LookupValue.  Note Detail.   Created on

1111.                Note 2.         3/9/24

2222.                Note 5.         3/5/24

3333.                Note 4.       3/3/24

I have tried group by but it will not let me group the lookup column. I get error that it's invalid.

ClearCollect(temp,GroupBy(NoteTable, LookupValue,"group"))

i have tried the below but it only provides the overall max record for the entire table and not by lookup value.

ClearCollect(MaxExeStatus,

    ForAll(

        Distinct(NoteTable,LookUpValue.LookUpID),

        {

        LookUpValue: ThisRecord.Value,

        MaxRecord: Last(FirstN(Sort(Filter(NoteTable, LookUpValue.LookUpID = LookUpValue.LookUpID), 'Created On', SortOrder.Descending),1))}

    ));

[Anonymous · Accepted solution]

Hi @drstrong ,

Thanks for @lbendlin and @Ashish_Mathur 's sharing.

You can create a measure in Power BI.

Measure = var _1= CALCULATE(MAX('Table'[createon]),FILTER(ALL('Table'),'Table'[lookupvalue]=SELECTEDVALUE('Table'[lookupvalue])))
 return
 IF(MAX('Table'[createon])=_1,_1,BLANK())

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Do you want to do this in Power Query?

[lbendlin]

This here is the Power BI forum.  Were you planning to post in the PowerApps forum?

Building Power Apps - Power Platform Community (microsoft.com)