Forum Discussion

AntBI26's avatar
AntBI26
Frequent Visitor
1 year ago
Solved

Error fetching data for the visual

Hello all,    I have a visual card that shows the overall time of some activities. The time is calculated by adding the sum of time from two different table for Time Activities and Time Activities2...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi AntBI26 ,

     

    You can modify the formula you provided at the beginning by replacing the hours minutes and seconds directly with the split down fields:

    TotalTimeFormatted1 = 
//VAR TimeString1 = [TimeActivities]
VAR Hours1 = MAX('YourTable'[TimeActivities - Copy.1])
VAR Minutes1 = MAX('YourTable'[TimeActivities - Copy.2])
VAR Seconds1 = MAX('YourTable'[TimeActivities - Copy.3])
VAR TotalSeconds1 = (Hours1 * 3600) + (Minutes1 * 60) + Seconds1

//VAR TimeString2 = [TimeActivities2]
VAR Hours2 = MAX('YourTable2'[TimeActivities - Copy.1])
VAR Minutes2 = MAX('YourTable2'[TimeActivities - Copy.2])
VAR Seconds2 = MAX('YourTable2'[TimeActivities - Copy.3])
VAR TotalSeconds2 = (Hours2 * 3600) + (Minutes2 * 60) + Seconds2

VAR TotalSeconds = TotalSeconds1 + TotalSeconds2
VAR Hours = TRUNC(TotalSeconds / 3600)
VAR Minutes = TRUNC(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN
FORMAT(Hours, "00") & ":" &
FORMAT(Minutes, "00") & ":" &
FORMAT(Seconds, "00")

     

    Best Regards,
    Zhu

     

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the reply from johnt75.

 

Hi AntBI26 ,

 

Did you troubleshoot the error as johnt75 said? Your formulas require that TimeString1 and TimeString2 be in hh:mm:ss text format. This is so that the hours, minutes and seconds can be parsed correctly and calculated accordingly. As you say, there may be a problem with the 2024 data format. To avoid this error, you can split the hours minutes and seconds by colons in the Power Query Editor, and then replace them with the variables you defined.

Split the time as shown:

Get three new columns:

 

Best Regards,
Zhu

 

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