cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BSXS Frequent Visitor
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
cs_skit Member
Member

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

Don't you have the solution already?

 

Use

 

NewColumn = 

SWITCH(TRUE();

expression1;"A";

expression2;"B";

"C")

BSXS Frequent Visitor
Frequent Visitor

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

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

cs_skit Member
Member

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

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

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

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

v-huizhn-msft Super Contributor
Super Contributor

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

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

BSXS Frequent Visitor
Frequent Visitor

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

 

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")

v-huizhn-msft Super Contributor
Super Contributor

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

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

BSXS Frequent Visitor
Frequent Visitor

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

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

v-huizhn-msft Super Contributor
Super Contributor

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

Hi @BSXS,

Could you please share your sample table for further analysis?

Best Regards,
Angelia

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 374 members 4,102 guests
Please welcome our newest community members: