cancel
Showing results 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

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

=>

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

=>

1 ACCEPTED SOLUTION
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
Blog
3 REPLIES 3
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.

New Member

Hi Owen,

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

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
Blog

Announcements

#### 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 Monthly Update - June 2024

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

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

Top Solution Authors
Top Kudoed Authors