cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
76gftstragfdsa
Regular Visitor

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
76gftstragfdsa
Regular Visitor

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

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.

View solution in original post

4 REPLIES 4
WolfBiber Responsive Resident
Responsive Resident

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

Hi, 

I don't really understand your problem.

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

Unbenannt.PNG

Super User IV
Super User IV

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

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


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

76gftstragfdsa
Regular Visitor

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

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:

PBIscreenshot.png

76gftstragfdsa
Regular Visitor

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

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.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors