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
Wise1
Helper I
Helper I

Convert time to fraction (15mins to 0.25)

Hello All,

 

 I am just experimenting with PowerBi and wanted to know if there was a way to convert time from a whole figure to a fraction? 

Problem iam having is when its adding up times pBI is adding 15 mins and 1 hr to the same "tally" 

 

I have tried messing around with IF statements and Modeling and just wondered if anyone would know if it is possible to have DAX add the total to look; 

 

IF time_spent_description = Minutes (add them) 

IF time_spent_descrition = Hours (add) 

 

then have the total show as 2 Hrs 

 

I have tried adding another column under Queries and working through other means, but because the data is being pulled from SQL directly it says i cant add or modify the Queries

 

 

DAXLookup.JPG

 

 

Thanks! 

 

 

 

 

1 ACCEPTED SOLUTION

@Wise1

 

In this scenario, I think you could add a numerator column based on the "time_spent_units_description" column with SWITCH() function.

 

Numerator= SWITCH(Table[time_spent_units_description],"Hour",1,"Minute",60,"Second",60*60)

Then you can just use the "time_spent" divide by Numerator.

 

 

Time in Fraction = Table[time_spent]/Table[Numerator]

Regards,

 

View solution in original post

6 REPLIES 6
Cavring
Helper I
Helper I

Hi @Wise1

 

One option is to convert everything into minutes with an IF-formula:

 

Create a new column:

Time minutes = if(Table1[units]="Hour";Table1[time_spent]*60;Table1[time_spent])

 

Another option is to create it into time using an IF formula:

Create a new column:

  • Time 2 = If (Table1[units]="Hour";Table1[time_spent] & ":00:00"; "00:" & Table1[time_spent] & ":00")
  • Convert it to time format
  • Create a measure Total time 2 = Sum(Time 2)
  • With this formule you obviously have the have a bit of control over how data are entered ( 120 minutes won' work well in this formula I think)

 

Hope this helps,

Espen

 

Hello, 

 

Thanks for taking the time to reply to me, i will have a play around with the forumula you sent

Problem i have is because iam pulling this data with DirectQuery, and i cant add any columns (unless there is a way iam not aware of to do this!) 

 

 

 

 

Hi @Wise1

There are two different "new columns".

  1. In the query when you are shaping your data
  2. In the Power BI desktop (where make alle you new measures and graphs)

In the formula in the previous post I've used the 2nd. Se creen shot:

add.PNG

 

Regards,

Espen

Apologies for the delay in my reply, its been busy few days with xmas upon us

 

 

I have been messing around with a number of different ways to represent this based on the formula you let me know about, but I keep getting errors when i try to reference the table

 

ie: 

Timeminutes = IF(;view_tm_audit[time_spent]*60;Table1[time_spent])

 

 

 

TableLookup.jpg

 

Thanks for the tip of being able to add a column under the powerbi desktop!

 

 

@Wise1

 

In this scenario, I think you could add a numerator column based on the "time_spent_units_description" column with SWITCH() function.

 

Numerator= SWITCH(Table[time_spent_units_description],"Hour",1,"Minute",60,"Second",60*60)

Then you can just use the "time_spent" divide by Numerator.

 

 

Time in Fraction = Table[time_spent]/Table[Numerator]

Regards,

 

thanks for explaining that! Sorry ive been enjoying the XMAS break so getting back into the swing of things for 2017

 

this works 🙂 

You sir are a wizard 

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.