Forum Discussion

cturle's avatar
cturle
New Member
2 years ago
Solved

Dax TIME function bug with Hour = 13

with hour = 14, all is OK :  define table myMinute = {12} Evaluate { ("Hour", 14), ("myMinute", SELECTEDVALUE(myMinute[Value])), ("check in TIME", TIME(14, SELECTEDVALUE(my...
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi cturle 

    This is indeed confusing since, on the face of it, the two time values should be equal 😞

     

    Since Date/Time values are stored as Decimal values , the values are stored approximately, and any equality-related comparisons can return unexpected results.

    https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types

    https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#accuracy-of-number-type-calculations

     

    In your second example, you can verify that there is a difference in the Decimal values stored by subtracting the two values and formatting in scientific notation for example, giving a difference of approximately -1.1102E-16.

     

    I can't explain in this specific case why the two time values are stored as slightly different values, other than point to the above documentation. Using SELECTEDVALUE to return a value from a (query) table presumably plays a role. Perhaps someone out there can explain the specifics in this case! 🙂

     

    Because of all this, for any equality-related comparisons of Date/Time or Decimal values, I would suggest either rounding the values or testing whether ABS ( difference ) < threshold.

     

    e.g.

     

     

     

    DEFINE
    TABLE myMinute = {
        12
    }
    VAR MyHour = 13
    VAR Threshold = 1E-6 -- small threshold
    VAR RoundingMultiple = 1E-6 -- small multiple for rounding

EVALUATE
VAR Time1 =
    TIME ( MyHour, SELECTEDVALUE ( myMinute[Value] ), 0 )
VAR Time2 =
    TIME ( MyHour, 12, 0 )
RETURN
    {
        ( "Hour", MyHour ),
        ( "myMinute", SELECTEDVALUE ( myMinute[Value] ) ),
        ( "check in TIME", Time1 = Time2 ),
        ( "check in TIME threshold", ABS ( Time1 - Time2 ) < Threshold ),
        ( "check in TIME rounded", MROUND ( Time1, RoundingMultiple ) = MROUND ( Time2, RoundingMultiple ) )
    }

     

     

     

    Regards

cturle's avatar
cturle
New Member
2 years ago


Hi Owen,

 

Thanks for your answer,

 

As you said, Time values are stored approximately as floating point numbers. This is why in my example, i don't store any Time value. I have no Time1 nor Time2. I only store an integer value (12). So "a priori" this is not the root problem, but ...

 

I have simplified again the problem removing the call to SelectedValue :

define 
  var T1 = TIME(13, max(rand(),12), 0)
  var T2 = TIME(13,12,0)
  var T3 = TIME(13, max(1,12), 0)
Evaluate
      { ("", 0),
        ("case minutes=max(rand(),12) : T1 - 0.55", T1 - 0.55),
        ("case minutes=12             : T2 - 0.55", T2 - 0.55),
        ("case minutes=max(1,12)      : T3 - 0.55", T3 - 0.55)
       }

 

Now, it is clearer. What i understand is that there exists a "pre-processor" which computes T2 ans T3 at "compile time" to optimize the code. These results are surely stored somewhere and, as you said, precision is lost. T1 is computed at "runtime" and is not stored ! => this is why T1 - 0.55 <> 0

 

So the diff seems to come from a test between a (stored, pre-processed) approximate value and a (not stored, computed) precise value

 

=> Loss of precision due to storing a decimal value is the technical root cause. You're right 🙂

 

Thanks and have a good day