Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I'm trying to sum the three below measures together, which forum member Icey kindly help with, but an recieving the following error.
Couldnt load the data for the visual - Calculation error cannot convert value of Type text to type number
MY measure which i am recieivng the error from is
Solved! Go to Solution.
Hi @Anonymous,
I found a typo on my formula in 'tsBK' variable('Calls SQL' should be 'Breaks' and I already modify on above post), you can modify measure formula to confirm if the wrong calculation is caused with this part.
Regards,
Xiaoxin Sheng
Hi @Anonymous,
Current DAX formulas do not support to use math operator on text values.
According to your DAX formulas, they will return merges text values that not able to do math calculation, you can only use text operation functions and logic operators to compare with different text values.
Please keep your duration as a numeric value(total second) and convert them after calculations instead convert before calculations.
Regards,
Xiaoxin Sheng
Ok that makes sense thanks for that, could you help with the formula??
HI @Anonymous ,
You can try to use the following measure formula:
Total time =
VAR tsCS =
SUMX (
'Calls SQL',
VAR _t1 =
TIMEVALUE ( 'Calls SQL'[Total Time on Calls] )
VAR _t2 =
TIMEVALUE ( 'Calls SQL'[Wrap Time] )
RETURN
( HOUR ( _t1 ) + HOUR ( _t2 ) ) * 3600
+ ( MINUTE ( _t1 ) + MINUTE ( _t2 ) ) * 60
+ SECOND ( _t1 )
+ SECOND ( _t2 )
)
VAR tsBK =
SUMX (
'Breaks',
VAR _t1 =
TIMEVALUE ( 'Breaks'[Minutes] )
RETURN
HOUR ( _t1 ) * 3600
+ MINUTE ( _t1 ) * 60
+ SECOND ( _t1 )
)
VAR ts = tsCS + tsBK
VAR _hour =
INT ( ts / 3600 )
VAR _minute =
INT ( MOD ( ts, 3600 ) / 60 )
VAR _second = ts - _hour * 3600 - _minute * 60
RETURN
_hour & ":"
& FORMAT ( _minute, "00" ) & ":"
& FORMAT ( _second, "00" )
Regards,
Xiaoxin Sheng
Thanks for this @v-shex-msft . I seem to be getting different results to what excel would show as a total.
Excel = 48:47:31
Power BI Measure = 499:47:08
Hi @Anonymous,
I found a typo on my formula in 'tsBK' variable('Calls SQL' should be 'Breaks' and I already modify on above post), you can modify measure formula to confirm if the wrong calculation is caused with this part.
Regards,
Xiaoxin Sheng
Amazing amazing amazing thanks very much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |