cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Use SWITCH with TIME() / time frames.

Hi guys,

I use this formula to calculate some timeframes.

Timeframe=
SWITCH(TRUE();
Patient[planned dismissal] > TIME(07;00;00) && Patient[planned dismissal] < TIME(15;00;00); TIME(15;00;00);
Patient[planned dismissal] > TIME(15;00;00) && Patient[planned dismissal] < TIME(23;00;00); TIME(23;00;00);
TIME(07;00;00))

It doesn't work, my outcome is always the 'else': 07:00. See image below + expected results. How do I handle this?

1 ACCEPTED SOLUTION
Super User

The question is about the data although you see the hours only the data behind is date so you need to get onlythe time part of Patient[Gepland ontslagtijd aangepast] column, redo your calculation to:

```Tijdsblok ontslag =
VAR Hour_Part =
TIME ( HOUR ( Patient[Gepland ontslagtijd aangepast] ); MINUTE ( Patient[Gepland ontslagtijd aangepast] ); SECOND ( Patient[Gepland ontslagtijd aangepast] ) )
RETURN
SWITCH (
TRUE ();
Hour_Part > TIME ( 07; 00; 00 )
&& Hour_Part < TIME ( 15; 00; 00 ); TIME ( 15; 00; 00 );
Hour_Part > TIME ( 15; 00; 00 )
&& Hour_Part < TIME ( 23; 00; 00 ); TIME ( 23; 00; 00 );
TIME ( 07; 00; 00 )
)```

Gives the result below:

I'm not addin the PBIX since it tell me it was done on a previous version so if I send it out to you it will not open.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

6 REPLIES 6
Super User

Is the planned dismissal column formatted as time or date?

In the test I have made it work correctly with your calculation.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper V

Hi @MFelix ,

Thanks for your reply. I see that in my formula

Timeframe=
SWITCH(TRUE();
Patient[planned dismissal] > TIME(07;00;00) && Patient[planned dismissal] < TIME(15;00;00); TIME(15;00;00);
Patient[planned dismissal] > TIME(15;00;00) && Patient[planned dismissal] < TIME(23;00;00); TIME(23;00;00);
TIME(07;00;00))

the colum Patient[planned dismissal] is based on a conditional column.

Patient[planned dismissal] =

Planned dismissal= IF(Patient[Gepland Ontslagdatum - Copy] = DATE(1900;1;1); NOW(); Patient[planned datetime])

In the image below, you can see the other columns. I've set the column Planned dismissal to 'time'.

So, to summarize: Timeframe is based on a calculated and a conditional column, changed to datatype 'time'.

Super User

Is it possible to share a sample file?

As I refer based on my tests everything should match.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

The question is about the data although you see the hours only the data behind is date so you need to get onlythe time part of Patient[Gepland ontslagtijd aangepast] column, redo your calculation to:

```Tijdsblok ontslag =
VAR Hour_Part =
TIME ( HOUR ( Patient[Gepland ontslagtijd aangepast] ); MINUTE ( Patient[Gepland ontslagtijd aangepast] ); SECOND ( Patient[Gepland ontslagtijd aangepast] ) )
RETURN
SWITCH (
TRUE ();
Hour_Part > TIME ( 07; 00; 00 )
&& Hour_Part < TIME ( 15; 00; 00 ); TIME ( 15; 00; 00 );
Hour_Part > TIME ( 15; 00; 00 )
&& Hour_Part < TIME ( 23; 00; 00 ); TIME ( 23; 00; 00 );
TIME ( 07; 00; 00 )
)```

Gives the result below:

I'm not addin the PBIX since it tell me it was done on a previous version so if I send it out to you it will not open.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper V

Thanks a lot for your help, @MFelix .

Helper V

Hi @MFelix , here is my sample file.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.