Distinct doesn't seem to return distinct values for date/times

[Julian1]

I have a table created using 

TimeTable=

GENERATE (

MyData,

GENERATESERIES([StartTime],[EndTime],1/24/60/60) 

)

to create a new table with a [value] column that shows one row per second for each row in MyData. Later in my project, I need to find the unique values in that [value] column. To do that, I used:

VAR ValueTable = SELECTCOLUMNS(TimeTable,"Value",[Value])

To get only the time column and then did:

DISTINCT(ValueTable)

Interestingly, I'm seeing repeated output like this:

6/25/2020 6:52:30 AM

6/25/2020 6:52:30 AM

6/25/2020 6:52:31 AM

6/25/2020 6:52:31 AM

6/25/2020 6:52:32 AM

6/25/2020 6:52:32 AM

6/25/2020 6:52:33 AM

6/25/2020 6:52:34 AM

6/25/2020 6:52:35 AM

6/25/2020 6:52:36 AM

The output of DISTINCT(ValueTable) seems to match what's in ValueTable itself, the DISTINCT isn't actually returning unique date/time values. Am I doing something wrong? 

[Greg_Deckler · Accepted solution]

@Julian1 I think I have run into something similar to this at some point but having trouble recalling exactly. In your table with the duplicates, try this, create a new column with something like: 

[DateTimeColumn] * 1.

Now, jack the number of decimal places to display up to like 20 and see if this comes down to a need to ROUND. Should be able to round to like 5 or 6 decimal places to ensure distinct values. 

[Julian1]

@Greg_Deckler , you're right! I was generating the series as you suggested here: 

ttps://community.powerbi.com/t5/Desktop/Properly-summing-up-time-spent-in-overlapping-appointments/m...

I created an additional column as you suggested and saw the same date with different values in later decimal places:  

That's not something I expected. I'll try ROUND and see how that helps. Thanks so much!

[Julian1]

Hi @Greg_Deckler ,

I dug into this and I'm not sure rouding will work. As an example, here are two rows with the same time but different time decimal values:

If I round to the 5th decimal place, the decimal values will match: 43983.33531. That will allow a successful deduplication using DISTINCT. However, going to 5 decimal places isn't enough for other rows:

and for others, it will cause different times to appear to be the same as they will have the same decimal value:

Any thoughts on other approaches? 

cause other rows that have different times to match, which shouldn't:

[Greg_Deckler]

@Julian1 Seems like your time component must include milliseconds or something. I've run into this before, that's why I know to check!! 🙂

[PaulDBrown]

@Julian1 

I was actually very surprised that different values were returned for the same date/time value. 
One (cumbersome) option might be to breakdown the date/time values into separate columns (date, hour, minute, seconds), apply the relevant calculations and then concatenate/add if need be.

but I'm just speculating...

[PaulDBrown]

@Julian1 

Not sure if this works, but what happens if you use:

TimeTable=

GENERATE (

MyData,

DISTINCT(GENERATESERIES([StartTime],[EndTime],1/24/60/60))

)

[AllisonKennedy]

Are you able to upload a sample file using OneDrive please?

Or explain further details. You have only given half of the DAX - what is the full expression that uses the VAR?

What table are you putting the result into that gives duplicate results?

How are [Start Time] and [End Time] defined?