cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jaypatel Helper I
Helper I

Filter data using datatime field according to Morning/Afternoon/Evening/Night

Hi all,

I want to filters my data using slicer from Datetime field. 

If my hours from 09:00 PM to 6:00 AM then it should be "Night", 6:00 AM to 12:00 PM then "Morning", 12:00 PM to 4:00 PM then "Afternoon" and if 4:00 PM to 9:00 PM should be "Evening".

 

Thanks,

Jay.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-alq-msft
Microsoft

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

Hi, @jaypatel 

 

In your example, the data type for timefield should be datetime and the data type for NewTime should be text. NewTime is created by the following formula.

NewTime = FORMAT('ExampleLogin'[timefield],"HH:MM:ss")

 

Here is my test file .

 

If possible, please show me your sample data. Do mask sensitive data before uploading.

 

 

Best Regards

Allan

 

View solution in original post

17 REPLIES 17
PR20048119 Memorable Member
Memorable Member

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

Could you please share table structure AND what field are you using in slicer.

 

Thanks,

Pravin

jaypatel Helper I
Helper I

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

Below is my table structure,  and I want to ad slicer on "timefield" column.

 Login_User.JPG

 

Thanks,

Jay.

PR20048119 Memorable Member
Memorable Member

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

measure=Switch(True(),if(selectedvalue(table[timefield])>="01:00" &&  selectedvalue(table[timefield])<="03:00","result1"

if(selectedvalue(table[timefield])>="03:00" &&  selectedvalue(table[timefield])<="06:00","result2"

if(selectedvalue(table[timefield])>="07:00" &&  selectedvalue(table[timefield])<="10:00","result3","Result4")

 

Update the condtions as per your requirement.

 

I am not sure why do want to use timefield in slicer.

 

Drag this measure to your table visual.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

 

jaypatel Helper I
Helper I

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

hey @PR20048119 ,

it shows syntax error.!

 

PR20048119 Memorable Member
Memorable Member

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

Hi @jaypatel 

 

I edited previous reply.

Now try above measure.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

jaypatel Helper I
Helper I

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

@PR20048119 

Hey,

I tried your given syntax, but it still throws an error of syntax incorrect.

 

Measure = SWITCH(TRUE(),
IF(SELECTEDVALUE(TableName[timefield])>="06:00:00" && SELECTEDVALUE(TableName[timefield]) < "11:59:59","Morning",
IF(SELECTEDVALUE(TableName[timefield])>="12:00:00" && SELECTEDVALUE(TableName[timefield]) < "17:59:59","Afternoon",
IF(SELECTEDVALUE(TableName[timefield])>="18:00:00" && SELECTEDVALUE(TableName[timefield]) < "20:59:59","Evening","Night")

 

Please find my measure, correct me if I am wrong.

 

Thanks,

Jay.

PR20048119 Memorable Member
Memorable Member

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

It's my bad
Sorry for my measure
And typing mistake.
Actually just remove that if...no need need of if statement there.
Try to type syntax by yourself to avoid errors.
Switch(true(),condition,result,alterresult)

Thanks,
Pravin
Microsoft v-alq-msft
Microsoft

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

Hi, @jaypatel 

 

Based on your description, I created data to reproduce your scenario.

b1.png

 

You may create a measure as follows.

 

TimeDes = 
var _currenttimefield = MAX('Table'[timefield])
var _hh = VALUE(LEFT(_currenttimefield,2))
var _mm = VALUE(MID(_currenttimefield,4,2))
var _ss = VALUE(RIGHT(_currenttimefield,2))

return
SWITCH(
    TRUE(),
    (_hh>=21&&_hh<23)&&(_mm>=0&&_mm<=59)&&(_ss>=0&&_ss<=59),"Night",
    (_hh>=0&&_hh<6)&&(_mm>=0&&_mm<=59)&&(_ss>=0&&_ss<=59),"Night",
    (_hh>=6&&_hh<12)&&(_mm>=0&&_mm<=59)&&(_ss>=0&&_ss<=59),"Morning",
    (_hh>=12&&_hh<16)&&(_mm>=0&&_mm<=59)&&(_ss>=0&&_ss<=59),"Afternoon",
    (_hh>=16&&_hh<21)&&(_mm>=0&&_mm<=59)&&(_ss>=0&&_ss<=59),"Evening",
    BLANK()
)

 

 

Result:

b2.png

 

Best regards

Allan

 

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

jaypatel Helper I
Helper I

Re: Filter data using datatime field according to Morning/Afternoon/Evening/Night

@PR20048119 

hey Pravin,

I removed "IF" condition from your given solution, but still, it throws error like it can not convert text to time format. 

 

Login_User_Error_20200221.jpg

 

Thanks,

Jay

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors