DISTINCT + UNIQUE of date/time does not produce unique results

[76gftstragfdsa]

I am experiencing a very strange issue where DISTINCT does not product unique values.

In one table which have some events (job executions) with some timestamps. I need to create another table with unique timestamps before and after an event happned.

My formula looks like this:

TempTable = 
    DISTINCT(
        UNION(
            SELECTCOLUMNS(VSTSJobs; "Time";  DATE( YEAR(VSTSJobs[queueTime]); MONTH(VSTSJobs[queueTime]); DAY(VSTSJobs[queueTime])) + TIME(HOUR(VSTSJobs[queueTime]); MINUTE(VSTSJobs[queueTime]); ROUND(SECOND(VSTSJobs[queueTime]);0)));
            SELECTCOLUMNS(VSTSJobs; "Time";  DATE( YEAR(VSTSJobs[queueTime]); MONTH(VSTSJobs[queueTime]); DAY(VSTSJobs[queueTime])) + TIME(HOUR(VSTSJobs[queueTime]); MINUTE(VSTSJobs[queueTime]); ROUND(SECOND(VSTSJobs[queueTime]);0)) - TIME(0;0;1))
        )
    )

Problem is, I have related this table to another table on the time column. And now that I load new values, it complains values are no longer unique despite using the DESTINCT function...

I figured this was caused by the source table having milliseconds in the timestamp (although PBI does not show it). Therefore I decided to split it up and round seconds (as shown above). But still there are duplicates...

I have now removed the relationship, so I can see the values.

Here's an example (notice row 2 & 3):

And the same formatted as a decimal number (row 2 & 3):

Why would DISTINCT not product unique values?

Did I misunderstand the purpose of DISTINCT, and is there another function that can produce truly unique values?

The two duplicate values originate from each element of the UNION construct.

As if the formula had been:

TempTable = 
    UNION(
        DISTINCT(...);
        DISTINCT(...);
    )

... but it is not.

If wonder if UNION could add some kind of hidden column indicating where each row was sourced from, and thereby preventing an outer DISTINCT to produce unique values across all sources.

[Greg_Deckler]

Will need example source data to recreate. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[76gftstragfdsa]

I don't see how to upload a file here...  but if you paste this into a table named VSTSJobs:

queueTime
16-04-2018 21:11:31
16-04-2018 21:11:31
16-04-2018 21:11:31
16-04-2018 21:11:31
16-04-2018 21:11:31
16-04-2018 21:11:30
16-04-2018 21:11:28
16-04-2018 21:11:28
16-04-2018 21:11:28
16-04-2018 21:11:27
16-04-2018 21:11:25

Then calculate another table with this:

TempTable = 
    DISTINCT(
        UNION(
            SELECTCOLUMNS(VSTSJobs; "Time";  DATE( YEAR(VSTSJobs[queueTime]); MONTH(VSTSJobs[queueTime]); DAY(VSTSJobs[queueTime])) + TIME(HOUR(VSTSJobs[queueTime]); MINUTE(VSTSJobs[queueTime]); ROUND(SECOND(VSTSJobs[queueTime]);0)));
            SELECTCOLUMNS(VSTSJobs; "Time";  DATE( YEAR(VSTSJobs[queueTime]); MONTH(VSTSJobs[queueTime]); DAY(VSTSJobs[queueTime])) + TIME(HOUR(VSTSJobs[queueTime]); MINUTE(VSTSJobs[queueTime]); ROUND(SECOND(VSTSJobs[queueTime]);0)) - TIME(0;0;1))
        )
    )

Then the output has two duplicated values.

And according to the status it has actually discovered this itself:

[76gftstragfdsa · Accepted solution]

Eventually I have wrapped it in yet another DISTINCT function, this way I have the unique results I need:

TempTable2 = DISTINCT(TempTable[Time])

I still wonder why DISTINCT(UNION(...)) does not work properly. If UNION adds some kind of hidden column indicating the source table for each row, it could explain it.

This formula specificially asks for a DISTINCT value in the Time column. Whereas the DISTINCT in the first formula does it on the entire table, although it should only has one coumn.

I tried something like this first: DISTINCT(UNION(...)[Time]), but couldn't figure out a syntax it would accept.

[WolfBiber]

Hi, 

I don't really understand your problem.

But datetime types are stored with milliseconds (look at the bottom of this image):