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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Need help with measure count

Hello everyone,

 

I'm doing a measurement that counts the number of tasks performed today. For the example, I put a copy for, so I circled all the start and end dates and I want to display that it actually, the current date, so for this example, the result is 3. 

Basically, a measure that allows you to count the number of tasks performed.

 

Captu.PNG

My best regards. Thank you. 

1 ACCEPTED SOLUTION

hi, @Anonymous 

For this scenario, you need to convert it to a date format not datetime format in the formula by DATE Function.

https://docs.microsoft.com/en-us/dax/date-function-dax

for example:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,DATE(YEAR(Sheet1[start time]),MONTH(Sheet1[start time]),DAY(Sheet1[start time]))<=TODAY() &&DATE(YEAR(Sheet1[end time]),MONTH(Sheet1[end time]),DAY(Sheet1[end time]))>=TODAY() ))

 

and if you want filter datetime is last 24 hours, you could add a conditinal DATEDIFF in formula

https://docs.microsoft.com/en-us/dax/datediff-function-dax

For example:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,DATEDIFF(Sheet1[end time],TODAY(),HOUR)<=24))

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

You could try this measure

measure = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[start time]<=TODAY() &&Sheet1[end time]>=TODAY() ))

 

Regards,

lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi, @v-lili6-msft  

Thank you for your answer, but in this measure we are filtering just on date and counting rows on the table that you named Sheet1, but the purpose of this measure is to calculate the number of tasks that i notice is not shown in the measure?  Regards.

hi, @Anonymous 

For some scenarios, COUNTROWS and COUNTA have the same logic.

So you could also try this formula:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,Sheet1[start time]<=TODAY() &&Sheet1[end time]>=TODAY() ))

 

And you mean that ( just on date) the conditional is "=" not "<=" or ">=" in the formula?

If so you could just adjust it by yourself.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you @v-lili6-msft, I have another question if you have a scenario that uses a date time, for example, 5/3/2019 05:43:47 and we would like to apply this measure do you think it works? Imagine that a task was executed yesterday at 23:45:08 and we would like to show the number of tasks that were executed for the last 24 hours I guess the measure will change ?! My best regards. Thank you again. 

hi, @Anonymous 

For this scenario, you need to convert it to a date format not datetime format in the formula by DATE Function.

https://docs.microsoft.com/en-us/dax/date-function-dax

for example:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,DATE(YEAR(Sheet1[start time]),MONTH(Sheet1[start time]),DAY(Sheet1[start time]))<=TODAY() &&DATE(YEAR(Sheet1[end time]),MONTH(Sheet1[end time]),DAY(Sheet1[end time]))>=TODAY() ))

 

and if you want filter datetime is last 24 hours, you could add a conditinal DATEDIFF in formula

https://docs.microsoft.com/en-us/dax/datediff-function-dax

For example:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,DATEDIFF(Sheet1[end time],TODAY(),HOUR)<=24))

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AndrejZitnay
Post Patron
Post Patron

Hello @Anonymous 

 

Can you try this?

 

measurement  = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[start time]=TODAY() ))

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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