cancel
Showing results for
Did you mean:

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.

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

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper V

Hi @MFelix ,

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

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

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors