Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mwen90
Helper III
Helper III

Automatically convert column data

Hi, 

 

Thanks for the help! I feel like this should be easy but I can't find the DAX. 

 

I have two columns (text) which display time like 12.25 and 13.50. I want to convert these automatically (as data comes in) so it display's in quarters so 12.15 and 13.30 so I can do time lost calculations. Ie. 13.30-12.15 = 1.15, so it's one hour and 15 lost. 

 

How do I convert these numbers?

 

Thanks for the help!

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @mwen90 ,

 

as you said the time is text. So you can split it up and compare.

Try the following calculated column:

Time Bucket =
VAR vHours = LEFT( myTable[TimeColumn], 2 )
VAR vMinutes = RIGHT( myTable[TimeColumn], 2 )
RETURN
    SWITCH(
        TRUE(),
        vMinutes > 0 && vMinutes <= 15,
            vMinutes & ":" & 15,
        vMinutes > 15 && vMinutes <= 30,
            vMinutes & ":" & 30,
        vMinutes > 30 && vMinutes <= 45,
            vMinutes & ":" & 45,
            vHours + 1 & ":" & 00
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

2 REPLIES 2
v-yetao1-msft
Community Support
Community Support

Hi @mwen90 

First, you need to change the two columns from integer format to time format .

Original data :

Ailsa-msft_0-1623305345278.png

Create calculated column :

time1 = FORMAT(TIME(TRUNC(Sheet1[Column1],0),(Sheet1[Column1]-TRUNC(Sheet1[Column1],0))*60,0),"")

time2 = FORMAT(TIME(TRUNC(Sheet1[Column2],0),(Sheet1[Column2]-TRUNC(Sheet1[Column2],0))*60,0),"")

The effect is as shown:

Ailsa-msft_1-1623305345278.png

Then calculated the diff between two time , and change the format .

diff time = FORMAT(Sheet1[time2]-Sheet1[time1],"hh:mm:ss")

The final result is as shown:

Ailsa-msft_2-1623305345279.png

I have attached my pbix file ,you can refer to it .

 

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

selimovd
Super User
Super User

Hey @mwen90 ,

 

as you said the time is text. So you can split it up and compare.

Try the following calculated column:

Time Bucket =
VAR vHours = LEFT( myTable[TimeColumn], 2 )
VAR vMinutes = RIGHT( myTable[TimeColumn], 2 )
RETURN
    SWITCH(
        TRUE(),
        vMinutes > 0 && vMinutes <= 15,
            vMinutes & ":" & 15,
        vMinutes > 15 && vMinutes <= 30,
            vMinutes & ":" & 30,
        vMinutes > 30 && vMinutes <= 45,
            vMinutes & ":" & 45,
            vHours + 1 & ":" & 00
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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