Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cturle
New Member

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(myMinute[Value]), 0) = TIME(14,12,0))
       }

=>

cturle_1-1694509419198.png

 

with hour = 13, it is KO

define 
  table myMinute = {12}
Evaluate
      { ("Hour", 13),
        ("myMinute", SELECTEDVALUE(myMinute[Value])),
        ("check in TIME", TIME(13, SELECTEDVALUE(myMinute[Value]), 0) = TIME(13,12,0))
       }

=> 

cturle_3-1694509679744.png

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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-c...

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
MargusMartsepp
New Member

Can you run 

Measure1 = TIME(13, SELECTEDVALUE(myMinute[Value]), 0)
Measure2 = TIME(13, 12, 0)
MeasureCheck = IF(Measure1 = Measure2, "OK", "KO")

 

If Measure1 and Measure2 are not equal, it suggests that the issue likely resides in how the TIME function is processing the hour value of 13 or how it's interacting with SELECTEDVALUE(myMinute[Value]).

 

If they are equal but your original expression still fails, the issue might be related to how the equality check is being performed in your original DAX expression.

cturle
New Member


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

cturle_0-1694537355784.png

 

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 

OwenAuger
Super User
Super User

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-c...

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.