Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table created using
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?
Solved! Go to 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 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.
@Greg_Deckler , you're right! I was generating the series as you suggested here:
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!
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:
@Julian1 Seems like your time component must include milliseconds or something. I've run into this before, that's why I know to check!! 🙂
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...
Proud to be a Super User!
Paul on Linkedin.
Not sure if this works, but what happens if you use:
Proud to be a Super User!
Paul on Linkedin.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |