CODEWITHSUNDEEP
powerbipowerbi-desktop
Hrvoje
Hrvoje

Reputation: 15152

Power BI duration filter for hours minutes and seconds

I have data in following format define as Time (hh:mm:ss) in Modeling tab:

enter image description here

When I try to turn on filter (Power BI filter or in filter in Visuals) all I can do is filter to day granularity. I tried some of the custom filters but none of them is working. How can I filter based on hh/mm/ss?

Upvotes: 0

Views: 2364

Answers (2)

Hrvoje
Hrvoje

Reputation: 15152

Acepted answer is data warehouse aproach with creation of separate tables and relations to time table. That's bit too long for me. But if you can do it easily like this:

Calculate duration using DATEDIFF formula:

time_in_sec= DATEDIFF([time_end];[time_start];SECOND)

Than you can use that to calculate your 3 custom columns for hours, minutes and seconds using this:

For hours:

duration_hh = if([time_in_sec]>0;FLOOR( [time_in_sec] / 3600; 1);0)

For minutes:

duration_mm = if([time_in_sec]>0;FLOOR( MOD( [hpm_trajanje_poziva_sec]; 3600) / 60; 1);0)

For seconds:

hpm_trajanje_poziva_ss = if([hpm_trajanje_poziva_sec]>0; FLOOR( MOD ( MOD( [hpm_trajanje_poziva_sec]; 3600) ;60 ); 1) ;0)

Than you can use those 3 calculated columns to filter your data in visuals.

Upvotes: 2

BarneyL
BarneyL

Reputation: 1362

The best approach would be to create a new time dimension, link it to your time fields and then use that to apply the filter rather than filtering those fields directly.

Here's a helpful guide giving a couple of was to apply this.

Upvotes: 1

Related Questions