How to think in LastN

[dalacarelia_196]

Hello LastN problem maybee 
In my table, I want to display the 3 latest entries per MemberID in my gallery  .
I was thinking about addcolumns or group by  and now iam just confused  

pls any hints is welcome here 

[SpongYe]

Hi @dalacarelia_196 

The LastN function returns the last set of records of a table; the second argument specifies the number of records to return. What you want needs a little adjustments to take the last 3 records per member.

This is my example data:

ClearCollect(
    colMembers,
    {ID: "1", MemeberID:"100", Meetingdate: Today()-4},
    {ID: "2", MemeberID:"100", Meetingdate: Today()-3},
    {ID: "3", MemeberID:"100", Meetingdate: Today()-2},
    {ID: "4", MemeberID:"100", Meetingdate: Today()-1},
    {ID: "5", MemeberID:"200", Meetingdate: Today()-3},
    {ID: "6", MemeberID:"200", Meetingdate: Today()-2},
    {ID: "7", MemeberID:"200", Meetingdate: Today()-1},
    {ID: "8", MemeberID:"300", Meetingdate: Today()-3},
    {ID: "9", MemeberID:"300", Meetingdate: Today()-2},
    {ID: "10", MemeberID:"300", Meetingdate: Today()-1}
)

This creates a coltestMembers that you can use in your gallery. 

Clear(coltestMembers);
With(
    { _colMember: Distinct(colMembers, MemeberID) },
    ForAll(
        _colMember,
        Collect( coltestMembers, LastN(Filter(colMembers, MemeberID = _colMember[@Value]), 3))
    )
)

Hope this solves your problem. 

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[dalacarelia_196]

Hello its complain about the ; after Clear(colMembers);

I am from sweden so thats ; instead of , digits 

;;Clear(coltestMembers);
With(
{ _colMember: Distinct(colMembers; MemeberID) };
ForAll(
_colMember;
Collect( coltestMembers; LastN(Filter(colMembers; MemeberID = _colMember[@Value]); 3))
)
)

[SpongYe]

@dalacarelia_196 

Thanks for the headup. Did my solution solved your issue? 

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.

Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[dalacarelia_196]

No its givng problem at the , The ClearCollect works out fine 


Clear(coltestMembers);
With(
{ _colMember: Distinct(colMembers; MemeberID) };
ForAll(
_colMember;
Collect( coltestMembers; LastN(Filter(colMembers; MemeberID = _colMember[@Value]); 3))
)
)

[SpongYe]

' its complain about the ; after Clear(colMembers);

For your issue use two semicolons (;;).

Clear(coltestMembers);;
With(
    { _colMember: Distinct(colMembers; MemeberID) };
    ForAll(
        _colMember;
        Collect( coltestMembers; LastN(Filter(colMembers; MemeberID = _colMember[@Value]); 3))
    )
)

Documentation you can find here: https://learn.microsoft.com/en-us/power-platform/power-fx/global#formula-separators-and-chaining-ope...
------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[dalacarelia_196]

In your code it is  and that gives med red dots 

Collect( coltestMembers; LastN(Filter(colMembers; MemeberID = _colMember[@Value]); 3))

so i chnged to ( in ) 
Collect(coltestMembers; LastN(Filter(colMembers; MemberID in _colMember.Value); 3))

ClearCollect(colMembers;
{ID: "1"; MemberID:"100"; Meetingdate:"2023-01-01"};
{ID: "2"; MemberID:"200"; Meetingdate:"2023-01-02"};
{ID: "3"; MemberID:"300"; Meetingdate:"2023-01-03"};
{ID: "4"; MemberID:"100"; Meetingdate:"2023-01-04"};
{ID: "5"; MemberID:"200"; Meetingdate:"2023-01-05"};
{ID: "6"; MemberID:"300"; Meetingdate:"2023-01-06"};
{ID: "7"; MemberID:"100"; Meetingdate:"2023-01-07"};
{ID: "8"; MemberID:"200"; Meetingdate:"2023-01-08"};
{ID: "9"; MemberID:"300"; Meetingdate:"2023-01-09"};
{ID: "10"; MemberID:"100"; Meetingdate:"2023-01-10"};
{ID: "11"; MemberID:"200"; Meetingdate:"2023-01-11"};
{ID: "12"; MemberID:"300"; Meetingdate:"2023-01-12"};
{ID: "13"; MemberID:"100"; Meetingdate:"2023-01-13"};
{ID: "14"; MemberID:"200"; Meetingdate:"2023-01-14"};
{ID: "15"; MemberID:"300"; Meetingdate:"2023-01-15"};
{ID: "16"; MemberID:"100"; Meetingdate:"2023-01-16"};
{ID: "17"; MemberID:"200"; Meetingdate:"2023-01-17"};
{ID: "18"; MemberID:"300"; Meetingdate:"2023-01-18"};
{ID: "19"; MemberID:"100"; Meetingdate:"2023-01-19"};
{ID: "20"; MemberID:"200"; Meetingdate:"2023-01-20"};
{ID: "21"; MemberID:"300"; Meetingdate:"2023-01-21"};
{ID: "22"; MemberID:"100"; Meetingdate:"2023-01-22"};
{ID: "23"; MemberID:"200"; Meetingdate:"2023-01-23"};
{ID: "24"; MemberID:"300"; Meetingdate:"2023-01-24"};
{ID: "25"; MemberID:"100"; Meetingdate:"2023-01-25"};
{ID: "26"; MemberID:"200"; Meetingdate:"2023-01-26"};
{ID: "27"; MemberID:"300"; Meetingdate:"2023-01-27"};
{ID: "28"; MemberID:"100"; Meetingdate:"2023-01-28"};
{ID: "29"; MemberID:"200"; Meetingdate:"2023-01-29"};
{ID: "30"; MemberID:"300"; Meetingdate:"2023-01-30"};
{ID: "31"; MemberID:"100"; Meetingdate:"2023-01-31"};
{ID: "32"; MemberID:"200"; Meetingdate:"2023-02-01"};
{ID: "33"; MemberID:"300"; Meetingdate:"2023-02-02"};
{ID: "34"; MemberID:"100"; Meetingdate:"2023-02-03"};
{ID: "35"; MemberID:"200"; Meetingdate:"2023-02-04"};
{ID: "36"; MemberID:"300"; Meetingdate:"2023-02-05"};
{ID: "37"; MemberID:"100"; Meetingdate:"2023-02-06"};
{ID: "38"; MemberID:"200"; Meetingdate:"2023-02-07"};
{ID: "39"; MemberID:"300"; Meetingdate:"2023-02-08"};
{ID: "40"; MemberID:"100"; Meetingdate:"2023-02-09"};
{ID: "41"; MemberID:"200"; Meetingdate:"2023-02-10"};
{ID: "42"; MemberID:"300"; Meetingdate:"2023-02-11"};
{ID: "43"; MemberID:"100"; Meetingdate:"2023-02-12"};
{ID: "44"; MemberID:"200"; Meetingdate:"2023-02-13"};
{ID: "45"; MemberID:"300"; Meetingdate:"2023-02-14"};
{ID: "46"; MemberID:"100"; Meetingdate:"2023-02-15"};
{ID: "47"; MemberID:"200"; Meetingdate:"2023-02-16"};
{ID: "48"; MemberID:"300"; Meetingdate:"2023-02-17"};
{ID: "49"; MemberID:"100"; Meetingdate:"2023-02-18"};
{ID: "50"; MemberID:"200"; Meetingdate:"2023-02-19"};
{ID: "51"; MemberID:"300"; Meetingdate:"2023-02-20"}

)

Clear(coltestMembers);;
With(
{ _colMember: Distinct(colMembers; MemberID) };
ForAll(
_colMember;
Collect(coltestMembers; LastN(Filter(colMembers; MemberID in _colMember.Value); 3))
)
)

Outcome is this 

But it sholud be 
The last three records for MemberID "100" should be records with IDs 43, 46, and 49.

The last three records for MemberID "200" should be records with IDs 44, 47, and 50.

The last three records for MemberID "300" should be records with IDs 45, 48, and 51.

[WarrenBelz · Accepted solution]

Hi @dalacarelia_196 ,

Yuo need to go a bit beyond where you are heading with this - try this

Clear(colTestMembers);
With(
   {
      _Members: 
      Sort(
         colMembers,
         Value(ID),
         SortOrder.Descending
      )
   },
   With(
      {
         _IDs: 
         GroupBy(
            _Members,
            "MemberID",
            "Data"
         )
      },
      ForAll(
         _IDs As _Items,
         Collect(
            colTestMembers,
            FirstN(
               Filter(
                  _Members,
                  MemberID = _Items.MemberID
               ),
               3
            )
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[dalacarelia_196]

Its problem in the For All Powerapps says 
Incompatible type. The collection cannot contain values of that type.

ClearCollect(colMembers;
{ID: "1"; MemberID:"100"; Meetingdate:"2023-01-01"};
{ID: "2"; MemberID:"200"; Meetingdate:"2023-01-02"};
{ID: "3"; MemberID:"300"; Meetingdate:"2023-01-03"};
{ID: "4"; MemberID:"100"; Meetingdate:"2023-01-04"};
{ID: "5"; MemberID:"200"; Meetingdate:"2023-01-05"};
{ID: "6"; MemberID:"300"; Meetingdate:"2023-01-06"};
{ID: "7"; MemberID:"100"; Meetingdate:"2023-01-07"};
{ID: "8"; MemberID:"200"; Meetingdate:"2023-01-08"};
{ID: "9"; MemberID:"300"; Meetingdate:"2023-01-09"};
{ID: "10"; MemberID:"100"; Meetingdate:"2023-01-10"};
{ID: "11"; MemberID:"200"; Meetingdate:"2023-01-11"};
{ID: "12"; MemberID:"300"; Meetingdate:"2023-01-12"};
{ID: "13"; MemberID:"100"; Meetingdate:"2023-01-13"};
{ID: "14"; MemberID:"200"; Meetingdate:"2023-01-14"};
{ID: "15"; MemberID:"300"; Meetingdate:"2023-01-15"};
{ID: "16"; MemberID:"100"; Meetingdate:"2023-01-16"};
{ID: "17"; MemberID:"200"; Meetingdate:"2023-01-17"};
{ID: "18"; MemberID:"300"; Meetingdate:"2023-01-18"};
{ID: "19"; MemberID:"100"; Meetingdate:"2023-01-19"};
{ID: "20"; MemberID:"200"; Meetingdate:"2023-01-20"};
{ID: "21"; MemberID:"300"; Meetingdate:"2023-01-21"};
{ID: "22"; MemberID:"100"; Meetingdate:"2023-01-22"};
{ID: "23"; MemberID:"200"; Meetingdate:"2023-01-23"};
{ID: "24"; MemberID:"300"; Meetingdate:"2023-01-24"};
{ID: "25"; MemberID:"100"; Meetingdate:"2023-01-25"};
{ID: "26"; MemberID:"200"; Meetingdate:"2023-01-26"};
{ID: "27"; MemberID:"300"; Meetingdate:"2023-01-27"};
{ID: "28"; MemberID:"100"; Meetingdate:"2023-01-28"};
{ID: "29"; MemberID:"200"; Meetingdate:"2023-01-29"};
{ID: "30"; MemberID:"300"; Meetingdate:"2023-01-30"};
{ID: "31"; MemberID:"100"; Meetingdate:"2023-01-31"};
{ID: "32"; MemberID:"200"; Meetingdate:"2023-02-01"};
{ID: "33"; MemberID:"300"; Meetingdate:"2023-02-02"};
{ID: "34"; MemberID:"100"; Meetingdate:"2023-02-03"};
{ID: "35"; MemberID:"200"; Meetingdate:"2023-02-04"};
{ID: "36"; MemberID:"300"; Meetingdate:"2023-02-05"};
{ID: "37"; MemberID:"100"; Meetingdate:"2023-02-06"};
{ID: "38"; MemberID:"200"; Meetingdate:"2023-02-07"};
{ID: "39"; MemberID:"300"; Meetingdate:"2023-02-08"};
{ID: "40"; MemberID:"100"; Meetingdate:"2023-02-09"};
{ID: "41"; MemberID:"200"; Meetingdate:"2023-02-10"};
{ID: "42"; MemberID:"300"; Meetingdate:"2023-02-11"};
{ID: "43"; MemberID:"100"; Meetingdate:"2023-02-12"};
{ID: "44"; MemberID:"200"; Meetingdate:"2023-02-13"};
{ID: "45"; MemberID:"300"; Meetingdate:"2023-02-14"};
{ID: "46"; MemberID:"100"; Meetingdate:"2023-02-15"};
{ID: "47"; MemberID:"200"; Meetingdate:"2023-02-16"};
{ID: "48"; MemberID:"300"; Meetingdate:"2023-02-17"};
{ID: "49"; MemberID:"100"; Meetingdate:"2023-02-18"};
{ID: "50"; MemberID:"200"; Meetingdate:"2023-02-19"};
{ID: "51"; MemberID:"300"; Meetingdate:"2023-02-20"}
)

;;Clear(colTestMembers)

;;With(
{
_Members:
Sort(
colMembers;
Value(ID);
SortOrder.Descending
)
};
With(
{
_IDs:
GroupBy(
_Members;
"MemberID";
"Data"
)
};
ForAll(
_IDs As _Items;
Collect(
colTestMembers;
FirstN(
Filter(
_Members;
MemberID = _Items.MemberID
);
3
)
)
)
)
)

[dalacarelia_196]

Thank you sir you guys always send me in to the right direction , misspelleing is realy hard and this jumping between languages 🙂 ..

But it keep my brain working 🙂

ClearCollect(colMembers;
{ID: "1"; MemberID:"100"; Meetingdate:"2023-01-01"};
{ID: "2"; MemberID:"200"; Meetingdate:"2023-01-02"};
{ID: "3"; MemberID:"300"; Meetingdate:"2023-01-03"};
{ID: "4"; MemberID:"100"; Meetingdate:"2023-01-04"};
{ID: "5"; MemberID:"200"; Meetingdate:"2023-01-05"};
{ID: "6"; MemberID:"300"; Meetingdate:"2023-01-06"};
{ID: "7"; MemberID:"100"; Meetingdate:"2023-01-07"};
{ID: "8"; MemberID:"200"; Meetingdate:"2023-01-08"};
{ID: "9"; MemberID:"300"; Meetingdate:"2023-01-09"};
{ID: "10"; MemberID:"100"; Meetingdate:"2023-01-10"};
{ID: "11"; MemberID:"200"; Meetingdate:"2023-01-11"};
{ID: "12"; MemberID:"300"; Meetingdate:"2023-01-12"};
{ID: "13"; MemberID:"100"; Meetingdate:"2023-01-13"};
{ID: "14"; MemberID:"200"; Meetingdate:"2023-01-14"};
{ID: "15"; MemberID:"300"; Meetingdate:"2023-01-15"};
{ID: "16"; MemberID:"100"; Meetingdate:"2023-01-16"};
{ID: "17"; MemberID:"200"; Meetingdate:"2023-01-17"};
{ID: "18"; MemberID:"300"; Meetingdate:"2023-01-18"};
{ID: "19"; MemberID:"100"; Meetingdate:"2023-01-19"};
{ID: "20"; MemberID:"200"; Meetingdate:"2023-01-20"};
{ID: "21"; MemberID:"300"; Meetingdate:"2023-01-21"};
{ID: "22"; MemberID:"100"; Meetingdate:"2023-01-22"};
{ID: "23"; MemberID:"200"; Meetingdate:"2023-01-23"};
{ID: "24"; MemberID:"300"; Meetingdate:"2023-01-24"};
{ID: "25"; MemberID:"100"; Meetingdate:"2023-01-25"};
{ID: "26"; MemberID:"200"; Meetingdate:"2023-01-26"};
{ID: "27"; MemberID:"300"; Meetingdate:"2023-01-27"};
{ID: "28"; MemberID:"100"; Meetingdate:"2023-01-28"};
{ID: "29"; MemberID:"200"; Meetingdate:"2023-01-29"};
{ID: "30"; MemberID:"300"; Meetingdate:"2023-01-30"};
{ID: "31"; MemberID:"100"; Meetingdate:"2023-01-31"};
{ID: "32"; MemberID:"200"; Meetingdate:"2023-02-01"};
{ID: "33"; MemberID:"300"; Meetingdate:"2023-02-02"};
{ID: "34"; MemberID:"100"; Meetingdate:"2023-02-03"};
{ID: "35"; MemberID:"200"; Meetingdate:"2023-02-04"};
{ID: "36"; MemberID:"300"; Meetingdate:"2023-02-05"};
{ID: "37"; MemberID:"100"; Meetingdate:"2023-02-06"};
{ID: "38"; MemberID:"200"; Meetingdate:"2023-02-07"};
{ID: "39"; MemberID:"300"; Meetingdate:"2023-02-08"};
{ID: "40"; MemberID:"100"; Meetingdate:"2023-02-09"};
{ID: "41"; MemberID:"200"; Meetingdate:"2023-02-10"};
{ID: "42"; MemberID:"300"; Meetingdate:"2023-02-11"};
{ID: "43"; MemberID:"100"; Meetingdate:"2023-02-12"};
{ID: "44"; MemberID:"200"; Meetingdate:"2023-02-13"};
{ID: "45"; MemberID:"300"; Meetingdate:"2023-02-14"};
{ID: "46"; MemberID:"100"; Meetingdate:"2023-02-15"};
{ID: "47"; MemberID:"200"; Meetingdate:"2023-02-16"};
{ID: "48"; MemberID:"300"; Meetingdate:"2023-02-17"};
{ID: "49"; MemberID:"100"; Meetingdate:"2023-02-18"};
{ID: "50"; MemberID:"200"; Meetingdate:"2023-02-19"};
{ID: "51"; MemberID:"300"; Meetingdate:"2023-02-20"}
)

;;Clear(colTestMembers);;
With(
{
_Members:
Sort(
colMembers;
Value(ID);
SortOrder.Descending
)
};
With(
{
_IDs:
GroupBy(
_Members;
"MemberID";
"Data"
)
};
ForAll(
_IDs As _Items;
Collect(
colTestMembers;
FirstN(
Filter(
_Members;
MemberID = _Items.MemberID
);
3
)
)
)
)
)