Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Julian1
Employee
Employee

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

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? 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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:  

Hugepickle_0-1600064308296.png

 

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

 

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:

Hugepickle_1-1600108017051.png

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:

Hugepickle_2-1600108488459.png

 

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

Hugepickle_3-1600108551374.png

 

Any thoughts on other approaches? 

 

 

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

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

@Julian1 

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

TimeTable=
GENERATE (
MyData,
DISTINCT(GENERATESERIES([StartTime],[EndTime],1/24/60/60))
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






AllisonKennedy
Super User
Super User

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?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.