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
BSXS
Frequent Visitor

New calculated column with IF/Switch function where data are from Time formated Column

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"

 

 

 

 

11 REPLIES 11
Ale
Resolver II
Resolver II

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.

cs_skit
Resolver IV
Resolver IV

Don't you have the solution already?

 

Use

 

NewColumn = 

SWITCH(TRUE();

expression1;"A";

expression2;"B";

"C")

BSXS
Frequent Visitor

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

 

switch Time error.JPG

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.


1.PNG

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.


2.PNG

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 

 

 

Skärmklipp.JPG

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

BSXS
Frequent Visitor

But are these expressions correct regarding Time format and can you do Switch on time column ?

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.