Getting Distinct Count of a column where the minimum value resides in a measure - Dax

[Anonymous_226]

Hi All,

Some context to start:

I am connected via direct query to a dataset so creating tables or calculated columns is not an option, as well as power query is not ethier.

I have the following measure which takes a date/time column and converts the date and time into an integer using a 24 hour clock format.
Example: Date/Time: 2/2/2022 7:07:01 AM Integer: 20220202070701

MINX(

SUMMARIZE(

TableA,

TableA[Number],

"MinStartInteger",

MINX(

FILTER(

TableA,

TableA[Number] = EARLIER(TableA[Number])

),

YEAR(TableA[Date/Time]) * 10000000000

+ MONTH(TableA[Date/Time]) * 100000000

+ DAY(TableA[Date/Time]) * 1000000

+ HOUR(TableA[Date/Time]) * 10000

+ MINUTE(TableA[Date/Time]) * 100

+ SECOND(TableA[Date/Time])

)

),

[MinStartInteger]

)

When I add this measure into a table with my other columns from TableA it works fine and shows the integer for each row as expected.

My issue is the TableA[Number] column can have multiple rows with the same Number and the same Date/Time.

What I need to be able to do is get the minimum Date/Time Integer for each unique number in the TableA[Number column]. If there is 2 rows with the same Date/Time I only want one of those rows...in the case of rows with the same number one of the rows usually has a blank value in a column called "stop" and the other row has an actual Date/Time in there so we can use that to help remove the duplicates with the same Date/Time.

I have been at this for quite so time now with no luck. Any help is greatly appreciated.

[Alef_Ricardo_]

Hi Anonymous_226,

It sounds like you want to get the minimum Date/Time Integer for each unique number in the TableA[Number] column, and if there are multiple rows with the same Date/Time, you only want to keep one of those rows. You mentioned that one way to remove duplicates with the same Date/Time is to use the “stop” column, where one of the rows usually has a blank value and the other row has an actual Date/Time.

[Anonymous_226]

@Alef_Ricardo_yes that is correct I want to get the minimum Date/Time Integer for each unique number in the TableA[Number] column the issue with the measure that i posted above is that it is also not giving me the minimum Date/Time for each unique number for example i can see a number show up twice that both have the same date but one has a time of 07:07:15 and the other has a time of 07:07:16 and they both get returned by the measure

[Alef_Ricardo_]

One way to achieve this is to create a new measure that filters out the rows with blank values in the “stop” column before calculating the minimum Date/Time Integer. Here’s an example of how you can do this:

MinDateTimeInteger =
VAR MinDateTime =
MINX (
SUMMARIZE (
FILTER ( TableA, NOT ( ISBLANK ( TableA[stop] ) ) ),
TableA[Number],
"MinStartInteger",
MINX (
FILTER ( TableA, TableA[Number] = EARLIER ( TableA[Number] ) ),
YEAR ( TableA[Date/Time] ) * 10000000000
+ MONTH ( TableA[Date/Time] ) * 100000000
+ DAY ( TableA[Date/Time] ) * 1000000
+ HOUR ( TableA[Date/Time] ) * 10000
+ MINUTE ( TableA[Date/Time] ) * 100
+ SECOND ( TableA[Date/Time] )
)
),
[MinStartInteger]
)
RETURN
MinDateTime

This measure first filters out the rows with blank values in the “stop” column using FILTER(TableA, NOT(ISBLANK(TableA[stop]))). Then it calculates the minimum Date/Time Integer for each unique number in the TableA[Number] column using the same logic as your original measure.

I hope this helps! Let me know if you have any further questions. 😊