Choosing Earliest User for the Total Orders Keyed by Users Count

[Anonymous]

Hello,

My Sales Transaction Table is something like this. I'm trying to chart out the percentile of Orders keyed by Users.

Order No	Line No	User	Date Updated	Time Updated
123	1	A	01 January 2022	10:00:00
123	1	A	02 January 2022	10:01:56
123	2	B	02 January 2022	10:05:11
123	5	B	02 January 2022	10:12:23
123	3	C	02 January 2022	10:10:35
123	4	C	02 January 2022	10:10:56
124	2	A	02 January 2022	14:14:12
124	2	A	02 January 2022	14:23:24
124	3	A	02 January 2022	15:00:01
124	4	B	02 January 2022	15:12:45
124	1	C	02 January 2022	14:12:00

The total number of Orders is 2. Each Order has multiple Users. 

So even when I want total orders by Users, It counts the same Order number multiple times.

I only want to count the earliest User for an Order.
Example: In the Data, For Order No 123, User A was the earliest, and for 124, User C was the earliest. 

So the graph should show 2 orders, 1 for A and C each. How can I do this in DAX?

Thank you in advance for any help!

[PaulOlding · Accepted solution]

Hi @Anonymous 

One option is to add a calculated column to identify the earliest row per order

Rank = 
RANKX(
    FILTER('Sales Transactions', 'Sales Transactions'[Order No] = EARLIER('Sales Transactions'[Order No]) ),
    'Sales Transactions'[Date Updated] + 'Sales Transactions'[Time Updated],
    ,
    ASC
    )

then a measure can use that Rank column to select the earliest user only

Order Count = 
CALCULATE(
    COUNTROWS('Sales Transactions'),
    KEEPFILTERS('Sales Transactions'[Rank] = 1)
)