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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
a10703
Helper I
Helper I

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

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

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

Amazing, thank you for your help Sir. 

Best regards!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.