Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Hi, @jaypatel
Based on your description, I created data to reproduce your scenario.
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:
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, @jaypatel
Here is my raw data in Power Query.
'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.
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.
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.
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.
Thanks,
Jay.
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.!
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
hey Pravin,
I removed "IF" condition from your given solution, but still, it throws error like it can not convert text to time format.
Thanks,
Jay
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.
and I Also tried with below measure,
Thanks,
Jay.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |