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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jaypatel
Helper III
Helper III

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

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
v-alq-msft
Community Support
Community Support

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.

Hi @v-alq-msft ,

As you've given, 

I tried with my table, but it also throws below error.

Login_User_Error_20200221_1808.jpg

 Thanks,

Jay.

Hi, @jaypatel 

 

Here is my raw data in Power Query.

c1.png

 

'timefield' is a calculated column created as follows.

timefield = FORMAT('Table'[DateTime],"HH:MM:ss")

 

Please try again as my first reply and show me your result. Thanks.

 

Best Regards

Allan

 

Hey, @v-alq-msft  Thank you for responding me, but as you mentioned in the previous reply I changed the Datatype of the main "timefield", and added new calculated column as "NewTime". But still, I am facing an error.

I have attached some images of what I have done in my Datasets, Please go through it and help me to resolve. 

 

Raw Data.JPG

 

NewTime.JPG

 

ResultError.JPG

 

 

Thanks,

Jay

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

 

Yes, @v-alq-msft , 

I was converting "Newtime" into "Time" format, and then apply measure for that column, That was the only reason behind the error. As you mentioned, I changed "NewTime" datatype to Text and issue resolved. Thank you so much.

 

Regards,

Jay.

Anonymous
Not applicable

Hi @jaypatel 

 

It looks like you are trying to compare string values with numeric values.

 

Check the data type your column may be some column are of type text. Chnage data type to Whole number.

 

For better understanding debug the dax step by step. check what values returned by each variable by creating seperate measure for them and adding them to card 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.

Anonymous
Not applicable

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

 

Thanks,

Pravin

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

 Login_User.JPG

 

Thanks,

Jay.

Anonymous
Not applicable

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.

 

hey @Anonymous ,

it shows syntax error.!

 

Anonymous
Not applicable

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.

@Anonymous 

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.

Anonymous
Not applicable

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

@Anonymous 

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

Anonymous
Not applicable

I think solution provided by @v-alq-msft  will work for you.

 

For my solution you have to use format function to make it in required fromat for comparision.

 

Like "10:00" need to be replaced by format("10:00","HH:MM")

 

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

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

Hi @Anonymous ,

I tried with FORMAT function to convert time to a specific format, but it is not what I want. And It still throws an error.

Login_User_Error_20200221_1825.jpg

and I Also tried with below measure,

 

TimeRangeNew = SWITCH(TRUE(),
FORMAT(SELECTEDVALUE(exampletableuser[timefield])>"06:00:00","HH:mm:ss") && FORMAT(SELECTEDVALUE(exampletableuser[timefield]) < "11:59:59","HH:mm:ss"),"Morning",
FORMAT(SELECTEDVALUE(exampletableuser[timefield])>="12:00:00","HH:mm:ss") && FORMAT(SELECTEDVALUE(exampletableuser[timefield]) < "17:59:59","HH:mm:ss"),"Afternoon",
FORMAT(SELECTEDVALUE(exampletableuser[timefield])>="18:00:00","HH:mm:ss") && FORMAT(SELECTEDVALUE(exampletableuser[timefield]) < "20:59:59","HH:mm:ss"),"Evening","Night")

 

Thanks,

Jay.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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