Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SOLVED: Count rows based on column values greater than record value (different column)

Hi All,

 

I'm able to do quite a bit (altough basic) in PowerBI however this riddle got me stunt ... I was able to produce the desired outcome in excel but it's inconvenient as the size of the dataset cripples my machine and so i need to make this work in PBI and so i'm seeking help.

 

This is what the data looks like and what i wand to do.

Order IDOrder dateShipping dateOrder typeongoing ordersongoing shirt orderongoind pants order
11/1/2021 12:04:00 AM1/3/2021 10:00:00 AMshirt000
21/3/2021 8:57:00 AM1/6/2021 2:00 PMshirt110
32/4/2021 7:23:00 AM2/6/2021 11:30:00 PMshirt000
42/5/2021 12:02:00 PM2/8/2021 7:00:00 AMpants110
52/6/2021 11:03:00 PM2/9/2021 7:00:00 AMpants211

 

The idea behind column "ongoing order" is to count the number of rows where the "shipping date" is after the current record's "order date", the other 2 column would split that same information based on "order type".

 

I tried a number of things but most of the time I end up with a circular reference.

 

 

Is there a way to even do this in Power BI ?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDIZfZfQ8aPKndTM3H0DYvfTgTS8ibu+Pdd26zoGHkPCRfElCMGxaw5YtCNwwlJwSNZfrjGXBlFjB4/3xnlKmErENBvVAr76rNKdM8UVDreAlskJSBetyc6eQosCqYFahg2Z/iZsH/PbRthe2X+3VQ6/bcxoP1/ifvSSV5vxHg6KK8QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, #"Order date" = _t, #"Shipping date" = _t, #"Order type" = _t, #"ongoing orders" = _t, #"ongoing shirt order" = _t, #"ongoind pants order" = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Order ID", Int64.Type}, {"Order date", type datetime}, {"Shipping date", type datetime}, {"Order type", type text}, {"ongoing orders", Int64.Type}, {"ongoing shirt order", Int64.Type}, {"ongoind pants order", Int64.Type}},"en-US"),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "oo", each Table.RowCount(Table.SelectRows(#"Modificato tipo", (r)=>r[Shipping date]>[Order date] and r[Order date]<[Order date] ))),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "oos", each Table.RowCount(Table.SelectRows(#"Modificato tipo", (r)=>r[Shipping date]>[Order date] and r[Order date]<[Order date] and r[Order type]="shirt" ))),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "oop", each Table.RowCount(Table.SelectRows(#"Modificato tipo", (r)=>r[Shipping date]>[Order date] and r[Order date]<[Order date] and r[Order type]="pants" )))
in
    #"Aggiunta colonna personalizzata2"

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    some clarifications:

    1) is the date format d / m / y or m / d / y?

    2) what do I mean by "the" shipping date "is after the current record's" order date ","? for example 02/06/2021 07:00:00 is after 02/06/2021 23:03:00?

    3) should the count be done on the whole table or limited to some subgroup of records? 

     

    PS

    how many rowsXcolumns has your table?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks a million for the speedy response, clarifications:

     

    1) the date format is M/D/Y

     

    2) on a specific row, the "order date" needs to be verified against all other record's "shipping date" and i need a count of record where "shipping date" is > "order date". I don't know if i'm being clear 🙂

     

    3) The count needs to be on the whole table.

     

    My table has about 40k record per day and i will need to keep the data to report trend for 13months. currently i have 43 days of record and the excel file is starting to slow down (8mn to open, 3mn to calulate) i only show a few columns here, but there are 23 columns in the set.

     

    Thanks again for the help !

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      2) on a specific row, the "order date" needs to be verified against all other record's "shipping date" and i need a count of record where "shipping date" is > "order date". I don't know if i'm being clear

       

      I don't understand how, with this rule, the values of the [Ongoing Order] column are obtained.

       

      Could you give a more specific example of this calculation, in the case, for example, of the 2 of line 5?

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello Anonymous ,

         

        For line 2:  The "order date" is : 1/3/2021 8:57:00 AM, i need to check all other orders where the shipping date / time is greater than this , the only one that fits is order 1, "shippping date" is 1/3/2021 10:00:00 AM therefore the count is 1

         

        For line 5: The "order date" is : 2/6/2021 11:03:00 PM, if you check all other order where the shipping date is greater, the order that fits are: 3 & 4 as their respective  "shipping date" are 2/6/2021 11:30:00 PM and 2/8/2021 7:00:00 AM which are both greater than the record order date so the count is 2. Order 3 is shirt and order 4 is pants so the numbers get into the right columns as well 🙂

         

        Thanks !