Error fetching data for the visual

[AntBI26]

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, see DAX below:

TotalTimeFormatted =

VAR TimeString1 = [TimeActivities]

VAR Hours1 = VALUE(LEFT(TimeString1, 2))

VAR Minutes1 = VALUE(MID(TimeString1, 4, 2))

VAR Seconds1 = VALUE(RIGHT(TimeString1, 2))

VAR TotalSeconds1 = (Hours1 * 3600) + (Minutes1 * 60) + Seconds1

VAR TimeString2 = [TimeActivities2]

VAR Hours2 = VALUE(LEFT(TimeString2, 2))

VAR Minutes2 = VALUE(MID(TimeString2, 4, 2))

VAR Seconds2 = VALUE(RIGHT(TimeString2, 2))

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")

I have a slicer for 'Years'; when each respective year is selected respectively, the visual for their time is displayed fine.

However, the visual card gives an error message when nothing is selected on the slicer (basically when it has to provide a value for all the years). This is the error message I received: 

"MdxScript(Model) (24,16) Calculation error in measure 'Sheet1[TotalTimeFormatted]: Cannot convert value ':0' of type Text to style Number."

I don't understand how it would work for a specific year, but then it throws this mistake when nothing is selected in the slicer. 

Also, to clarify I started getting this error when a new file for 2024 was added to a specific folder (folder is my data source).  

[AntBI26]

Hello Zhu, thanks for your response. 

After following your method, I now receive this error message:

the measure concerns with the error are: 

TimeActivities =

VAR TotalSeconds=SUMX('Planner files for processing',HOUR('Planner files for processing'[Time.1])*3600+MINUTE('Planner files for processing'[Time.2])*60+SECOND('Planner files for processing'[Time.3]))

VAR Days =TRUNC(TotalSeconds/3600/24)

VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)

VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)

VAR Secs = MOD(TotalSeconds,60)

return IF((Hors + (Days*24))<10,"0"&(Hors + (Days*24)),(Hors + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

I am not sure where I am going wrong. 

[Anonymous · Accepted solution]

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]

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.

[johnt75]

It sounds like you have some badly formatted data in the new file, which doesn't have a correct date stamp. Try creating a table visual with some columns from your data, no measures, and then filter it so that year is blank. That should show the rows which are incorrect.