cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a10703
Frequent Visitor

Count rows that meet these requirements

Hi Guys,

 

I have the dataset one excell file with schedule update

 

I have collumn start and time for my filters . But i need one function, i need to count all the rows if they have time range 20:00:00  hours to 22:00:00 in all days excepted satuday, because i need to know too if the day is saturday count all range hour 08:00 to 22:00 hours. It's possible?

 

My collumn on dataset

 

01.PNG

2 ACCEPTED SOLUTIONS

Thanks, that's much easier to work with.

See attached PBIX file.

Let me know if you have any questions.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

Click on the 'Transform Data' button and see the individual steps, or see the code below.

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZdBLCsAgDATQq5SsBfPRRnMV8f7XaIW0oq4fMzDTGlRECEAaUSIj80XGAj0MoVXUUF14l5RcZJViTC5pF2GXPKT8wmj0he6TspOeVJzKRjQL66A616K9B/T+AA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, DATA = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"ID", Int64.Type}, {"DATA", type datetime}}
  ),
  #"Inserted Day Name" = Table.AddColumn(
    #"Changed Type",
    "Day Name",
    each Date.DayOfWeekName([DATA]),
    type text
  ),
  #"Inserted Time" = Table.AddColumn(
    #"Inserted Day Name",
    "Time",
    each DateTime.Time([DATA]),
    type time
  ),
  #"Added Custom" = Table.AddColumn(
    #"Inserted Time",
    "Custom",
    each
      if [Day Name] = "Saturday" and [Time] >= #time(8, 0, 0) and [Time] <= #time(22, 0, 0) then
        1
      else if [Day Name] <> "Saturday" and [Time] >= #time(20, 0, 0) and [Time] <= #time(22, 0, 0) then
        1
      else
        0
  )
in
  #"Added Custom"
Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

6 REPLIES 6
KNP
Super User
Super User

Yes it's possible.

Can you please paste some actual data (not a screenshot)?

Also, a screenshot of your data model would be useful?

Are you using a date dimension?

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
a10703
Frequent Visitor

Hi Dear Friend,

Thank you for your fast asnwer,

My data in excell is:

Data: format as date and time 

IDDATA
900

17/03/2022 01:23

90117/03/2022 07:07
90217/03/2022 07:44
90317/03/2022 08:21
90417/03/2022 08:32
905

18/03/2022 20:11

90618/03/2022 20:15
90718/03/2022 20:18
90818/03/2022 21:11

 

This helps?

Thanks, that's much easier to work with.

See attached PBIX file.

Let me know if you have any questions.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
a10703
Frequent Visitor

Hi KNP

how do i see as formulas? or you can explain me how you did? 

Best regards,

Click on the 'Transform Data' button and see the individual steps, or see the code below.

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZdBLCsAgDATQq5SsBfPRRnMV8f7XaIW0oq4fMzDTGlRECEAaUSIj80XGAj0MoVXUUF14l5RcZJViTC5pF2GXPKT8wmj0he6TspOeVJzKRjQL66A616K9B/T+AA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, DATA = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"ID", Int64.Type}, {"DATA", type datetime}}
  ),
  #"Inserted Day Name" = Table.AddColumn(
    #"Changed Type",
    "Day Name",
    each Date.DayOfWeekName([DATA]),
    type text
  ),
  #"Inserted Time" = Table.AddColumn(
    #"Inserted Day Name",
    "Time",
    each DateTime.Time([DATA]),
    type time
  ),
  #"Added Custom" = Table.AddColumn(
    #"Inserted Time",
    "Custom",
    each
      if [Day Name] = "Saturday" and [Time] >= #time(8, 0, 0) and [Time] <= #time(22, 0, 0) then
        1
      else if [Day Name] <> "Saturday" and [Time] >= #time(20, 0, 0) and [Time] <= #time(22, 0, 0) then
        1
      else
        0
  )
in
  #"Added Custom"
Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
a10703
Frequent Visitor

Amazing, thank you for your help Sir. 

Best regards!

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.