topic Re: compare text to time in DAX Commands and Tips

compare text to time

AI14 — Sun, 05 Feb 2023 09:45:59 GMT

i need to see if my Actual is less than my target but they are in different formats. both are measures however 1 has been formatted into HH:MM so its a text and the other is a normal short time.

e.g.

the text time measure is as follows: Actual= Format(example),"hh:mm) - result is 02.45

the Target measure might say 03:55 (in short time format)

the if formula is not working due to formatting issues :

if(actual<target,1,0)

please assist

Re: compare text to time

FreemanZ — Sun, 05 Feb 2023 09:57:15 GMT

hi AI14

try like:

Measure = IF( VALUE([Actual])< [Target], 1, 0 )

verified with such data:

Actual = FORMAT(TIME(2,45,0), "hh:mm") Target = TIME(1,55,0)

Re: compare text to time

tamerj1 — Sun, 05 Feb 2023 09:59:15 GMT

Hi AI14
You can use
IF ( TIMEVALUE ( actual ) < target, 1, 0 )

Re: compare text to time

FreemanZ — Sun, 05 Feb 2023 09:59:19 GMT

change the target, returns 1

Re: compare text to time

AI14 — Sun, 05 Feb 2023 11:13:12 GMT

Tried this but says i cant convert value " of type txt to type date

Re: compare text to time

AI14 — Sun, 05 Feb 2023 11:13:23 GMT

also Tried this but says i cant convert value " of type txt to type date

Re: compare text to time

FreemanZ — Sun, 05 Feb 2023 11:29:16 GMT

hi AI14

can you paste your data?

Re: compare text to time

AI14 — Sun, 05 Feb 2023 17:07:32 GMT

the actual:

Duration =

VAR _ref = MAX(a[REF])

VAR _time1 =

MAXX( FILTER( ALL(a), a[REF] = _ref ), [ST])

VAR _time2 =

MINX( FILTER( ALL(a), a[REF] = _ref ), [ST])

RETURN

FORMAT(_time2 - _time1, "hh:mm")

the Target: (i have this as a measure and a column, both not worked)

Target = 'Task Rules'[Earliest Start]+'Task Rules'[Duration]+'Task Rules'[Endbefore]+'Task Rules'[Setup]+'Task Rules'[Cleanup]

Re: compare text to time

tamerj1 — Sun, 05 Feb 2023 17:57:38 GMT

Hi AI14

try to use TIMEVALUE function to convert Duration to datetime data type as follows

Duration =
VAR _ref =
MAX ( a[REF] )
VAR _time1 =
MAXX ( FILTER ( ALL ( a ), a[REF] = _ref ), [ST] )
VAR _time2 =
MINX ( FILTER ( ALL ( a ), a[REF] = _ref ), [ST] )
RETURN
TIMEVALUE ( FORMAT ( _time2 - _time1, "hh:mm" ) )