Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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:
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.
Hi,
I don't really understand your problem.
But datetime types are stored with milliseconds (look at the bottom of this image):
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |