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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors