Forum Discussion

Julian1's avatar
Julian1
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

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? 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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. 

7 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    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?
  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Julian1 

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

    TimeTable=
    GENERATE (
    MyData,
    DISTINCT(GENERATESERIES([StartTime],[EndTime],1/24/60/60))
    )
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Julian1
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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-p/1333157/highlight/true#M575349.  

       

      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!

       

      • Julian1's avatar
        Julian1
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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: