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
I have a 24h time column where I want to classify each row into a new column.
My time column looks like 01:33 etc and is formated as Time
NewColumn =
if('data'[Time]<=07:00 && 'data'[Time]>=17:30, "A"),
if('data'[Time]<=06:00 && 'data'[Time]>=07:00, && 'data'[Time]<=17:30 && 'data'[Time]>=22:00, "B"),
also would like to have all other time values into "C"
Not sure if this was solved or not, but:
-
NewColumn =
if('data'[Time]<=07:00 && 'data'[Time]>=17:30, "A"),
if('data'[Time]<=06:00 && 'data'[Time]>=07:00, && 'data'[Time]<=17:30 && 'data'[Time]>=22:00, "B"),
also would like to have all other time values into "C"
-
It's impossible for any time be smaller than 7:00 and bigger than 17:30. that's why you get alwasy C.
Don't you have the solution already?
Use
NewColumn =
SWITCH(TRUE();
expression1;"A";
expression2;"B";
"C")
Iam gettin C in all rows with this;
NewColumn =
SWITCH(TRUE();
'data'[Time]<=0700 && 'data'[Time]>=1730;"A";
'data'[Time]<=0600 && 'data'[Time]>=0659 && 'data'[Time]<=1730 && 'data'[Time]>=2159;"B";
"C")
The column is time formatted and look like 14:33
Hi @BSXS,
Please use the TIME function, modify your formula to the one below, and checj=k if it works fine.
NewColumn = SWITCH(TRUE(); 'data'[Time]<=TIME(7,0,0) && 'data'[Time]>=TIME(17,30,0), "A"; 'data'[Time]<=TIME(6,0,0) && 'data'[Time]>=TIME(6,59,0) && 'data'[Time]<=TIME(17,30,0) && 'data'[Time]>=TIME(21,59,0);"B"; "C")
Best Regards,
Angelia
Hi and thanks
I had to reformat To local TIME which is ; instead of , but still have problem. I only get C as answer in all rows:
switch = SWITCH(TRUE(); 'data'[Time]<=TIME(07;00;00) && 'data'[Time]>=TIME(17;30;00); "A"; 'data'[Time]<=TIME(06;00;00) && 'data'[Time]>=TIME(06;59;00) && 'data'[Time]<=TIME(17;30;00) && 'data'[Time]>=TIME(21;59;00);"B"; "C")
Hi @BSXS,
After test, the Time fucntion is right. The logic is wrong. The condition "'data'[Time]<=TIME(07,00,00) && 'data'[Time]>=TIME(17,30,00)" is always false. Please see the following screenshot.
If I change the "'data'[Time]<=TIME(07,00,00) && 'data'[Time]>=TIME(17,30,00)" to "'data'[Time]<=TIME(07,00,00)", it returns A, please see the following screenshot.
Best Regards,
Angelia
Thanks, yes logic is wrong but how to write the logic with switch then I want to have various values BETWEEN certain hours of the day? I just thought it was && but Iam new to DAX.
best regards
Michael
Hi @BSXS,
Could you please share your sample table for further analysis?
Best Regards,
Angelia
Hi data is very simple, I just made an excel file with 36 rows, import and made a Time formatted column. Logic works but not if I want to have time between 07:00 to 17:30 and 06:00 to 06:59, It will just return C as value
if you get all C your filter expressions are wrong for A and B because they return 0 rows
you can also test your expressions seperately with some rowcount filter measures if they are correct
But are these expressions correct regarding Time format and can you do Switch on time column ?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |