How to get count based on text values in corresponding column

[Anonymous]

Hi, I have below table with ID, Status and TimeStamp. I would like to get count of ID for last 24 hours and last 365 days by filtering over the text in Status column.

ID                            Status                                     Timestamp

ZZ09                     Allowed for first- 32                     1-2-2019 7:00

ZZ09                     Allowed for first- 44                    1-2-2019 8:15

ZZ09                     Left for first - 33                          1-2-2019 9:15

ZZ09                     Allowed for first- 26                    1-2-2019 18:00

ZZ10                     Working for first- 44                   1-2-2019 8:15

ZZ10                     Left for first - 33                          2-2-2019 9:15

ZZ10                     Working for first- 32                   3-2-2019 7:00

ZZ11                     Error for first- 44                         1-2-2019 8:15

ZZ11                     Last schedule for first - 33          1-2-2019 9:15

ZZ11                     Error for first- 90                         1-2-2019 8:30

ZZ11                     Error for first- 89                         1-2-2019 10:15

So I want count of ZZ09 where status is "Allowed for first" and for ZZ10 where status is" Working for first" and for ZZ11  where status is" Error for first". and so on for different IDs.

Then I would like to get a count on the number for last 24 hours and last 365 days. Please let me know what steps to be followed in this case.

Thanks

[v-yuezhe-msft]

@Anonymous,

Firstly, create the following column in your table.

newstatus = LEFT(Table[Status ],FIND("-",Table[Status ],1,1))

Secondly, create the measures  below in your table.

last24hourcount = CALCULATE(COUNT(Table[ID]),FILTER(Table,Table[Timestamp]>=NOW()-1&&Table[Timestamp]<=NOW()))

last365dayscount = CALCULATE(COUNT(Table[ID]),FILTER(Table,Table[Timestamp]>=NOW()-365&&Table[Timestamp]<=NOW()))

Thirdly, create a slicer using newstatus column and use it to filter the your visuals.



Regards,
Lydia

[Anonymous]

How can I do the filter on "Id" instead of Status?? Customer is looking to get all Id of that partcular status.

[v-yuezhe-msft]

@ankku ,

You can create a slicer using ID field. If you still have questions, please post expected result here based on  the above sample data.

Regards,
Lydia