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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help needed with creating SelectRows and Group function

Hi,

 

I need help with creating a function. 

 

This is my sample data:

 

session_startsession_userDaily_sum
9/20/2023Person 14
9/22/2023Person 15
9/25/2023Person 18
9/23/2023Person 23
9/25/2023Person 25
9/26/2023Person 24
9/27/2023Person 27

 

I need to find out the session_start date when the sum of each of the session_user goes below 10, calculated backwards from today.
In the sample data calculated from today (9/28/2023) backwards for Person 1 the date when the sum of Daily_sum values goes below 10 is 9/23/2023 (because 9/22/2023 adds 5)  and Person 2's 9/27/2023 (because 9/26/2023 adds 4). I also need to be able to set how many days into the past the data is calculated.

I figured that I need to make the following two steps multiple times. First select the data that are in the past n days: (here 10 days) Table.SelectRows(Source, each Date.IsInPreviousNDays([session_start], 10)).


Then group those by "session_user":
Table.Group(Custom, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}).

 

Then I need to select the rows from the data that are in the past n - 1 days. Then group those by "session_user" and so on until today.

 

I was able to create a custom function that finds the value how many loops it takes for any of the values for session_users is below 10.

 

Here is the function:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
  #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}),


    CustomFunction = (n as number) as table =>
        let
            FilteredRows = Table.SelectRows(#"Changed column type", each Date.IsInPreviousNDays([session_start], n)),
            GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}),
            Result = if List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(GroupedRows, "Final N Value", each n) else @CustomFunction(n-1)
        in
            Result,
    FinalResult =
CustomFunction(10)
in
    FinalResult

 

 

 

 Here is the result at the moment:

ramzez_0-1695891576947.png

 

The function has stopped because Person 1's sum is less than 10 but at the same time I need to find out when Person 2's sum goes below 10.

 

How can I achieve this?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you for the reply!

I only want to take into account past days and not today so Date.IsInPreviousNDays should serve my purpose.

 

I've been able to refine my function as follows:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
  #"Promoted headers" = 
Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}),

    CustomFunction = (n as number, user as text, initialN as number) as table =>
    let
      FilteredRows = Table.SelectRows(#"Changed column type", each [session_user] = user and Date.IsInPreviousNDays([session_start], n)),
      GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}),
      Result = if Table.IsEmpty(GroupedRows) or List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(Table.AddColumn(GroupedRows, "Final N Value", each n), "First Try", each n = initialN) else @CustomFunction(n-1, user, initialN)
    in
      Result,

  Users = List.Distinct(#"Changed column type"[session_user]),
  DaysToFetch = 60,
  FinalResult = Table.FromRecords(List.Combine(List.Transform(Users, each Table.ToRecords(CustomFunction(DaysToFetch, _, DaysToFetch))))),
  Custom = Table.AddColumn(FinalResult, "Custom", each Date.AddDays( Date.From(DateTime.FixedLocalNow()),-[Final N Value]), type date),
  #"Custom 1" = 
Table.AddColumn(#"Custom", "not_valid_anymore", each Date.AddDays( Date.From(DateTime.FixedLocalNow()), if [First Try] = true then 0 else DaysToFetch - [Final N Value]), type date)
in
  #"Custom 1"

This pretty much gets me what I need.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Danger Danger Danger.  

 

Date.IsInPreviousNDays  does not include Today !!! 

 

Use a different function.

 

You also will want to group by person a lot earlier.

Anonymous
Not applicable

Thank you for the reply!

I only want to take into account past days and not today so Date.IsInPreviousNDays should serve my purpose.

 

I've been able to refine my function as follows:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
  #"Promoted headers" = 
Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}),

    CustomFunction = (n as number, user as text, initialN as number) as table =>
    let
      FilteredRows = Table.SelectRows(#"Changed column type", each [session_user] = user and Date.IsInPreviousNDays([session_start], n)),
      GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}),
      Result = if Table.IsEmpty(GroupedRows) or List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(Table.AddColumn(GroupedRows, "Final N Value", each n), "First Try", each n = initialN) else @CustomFunction(n-1, user, initialN)
    in
      Result,

  Users = List.Distinct(#"Changed column type"[session_user]),
  DaysToFetch = 60,
  FinalResult = Table.FromRecords(List.Combine(List.Transform(Users, each Table.ToRecords(CustomFunction(DaysToFetch, _, DaysToFetch))))),
  Custom = Table.AddColumn(FinalResult, "Custom", each Date.AddDays( Date.From(DateTime.FixedLocalNow()),-[Final N Value]), type date),
  #"Custom 1" = 
Table.AddColumn(#"Custom", "not_valid_anymore", each Date.AddDays( Date.From(DateTime.FixedLocalNow()), if [First Try] = true then 0 else DaysToFetch - [Final N Value]), type date)
in
  #"Custom 1"

This pretty much gets me what I need.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.