How to combine Summarize (GroupBy), Min Date and filter the rest of the Data?

[Anonymous]

Hello guys, 

I have the below set of data on my primary table "Table1"

Cat1	Cat2	Cat3	InputDate	Username
A	AB	ABC	1/1/2019	USER1
A	AB	ABC	5/1/2019	USERA
A	AB	ABC	10/1/2019	USERB
X	XB	XBC	1/2/2019	USER2
X	XB	XBC	5/2/2019	USERX
X	XB	XBC	10/2/2019	USER3
X	XB	XBC	15/2/2019	USER4
X	XB	XBC	20/2/2019	USERY
Y	YB	YBC	1/3/2019	USER3
Y	YB	YBC	5/3/2019	USERY
Y	YB	YBC	10/3/2019	USERB

And I want to make a new table that will keep only these lines that have the earliest Input Date from every set of Cat1/Cat2/Cat3.

So I want to have the above result

Cat1	Cat2	Cat3	InputDate	Username
A	AB	ABC	1/1/2019	USER1
X	XB	XBC	1/2/2019	USER2
Y	YB	YBC	1/3/2019	USER3

What I have to until now:

New Table = SUMMARIZE('Table1';'Table1'[Ca1];'Table1'[Cat2];'Table1'[Cat3];"Min_Input_Date";min(''Table1'[InputDate]))

And my results are:

Cat1	Cat2	Cat3	InputDate
A	AB	ABC	1/1/2019
X	XB	XBC	1/2/2019
Y	YB	YBC	1/3/2019

So when I try to bring also the appropriate Username from the line of the earliest Input Date I get confused.

I tryed some Filter fuctions after my research here but I didn't get any results. 

Can you help me please?

P.S I know how to do it with Power Query Editor.

Nevertheless, I need this new table (filtering) to be produced evertime I change the Input Date filters, (to look like something "Live", in terms of Input Date data) and then connect (create a relationship) this new "Earliest" input date Dimention with original Input date dimention and "charge" the earliest Username for every Cat1/Cat2/Cat3 sequence for the specific Input dates I choose every time.

Thanks In Advance guys!!!

I know you won't let me down!!

[d_gosbell]

---

@Anonymous wrote:

Nevertheless, I need this new table (filtering) to be produced evertime I change the Input Date filters, (to look like something "Live", in terms of Input Date data) and then connect (create a relationship) this new "Earliest" input date Dimention with original Input date dimention and "charge" the earliest Username for every Cat1/Cat2/Cat3 sequence for the specific Input dates I choose every time.

---

So a calculated table will not work this way. Caculated tables only update when you refresh your data, they do not change with changes to filters or slicers (they behave the same as a table built using Power Query). If I understand what you want correctly the way to achieve what you want would be to create a measure to get the min date and the user on the min date. 

The min date measure is simple

Min Date = MIN( 'Table'[InputDate])

Then you just want to get the user for the min date which you could do with the following (I'm just using the MIN() over the user name as a safety mechanism in case there are multiple users on the one date)

Min Date User = 
var _minDate = [Min Date]
RETURN CALCULATE( MIN('Table'[Username]), 'Table'[inputDate] = _minDate)

[Anonymous]

Dear d_gosbell

If I get your answer correct, I created a new Table as

New Table = Summarize (table1;cat1;cat2;cat3;inputdate;username) (no min date, just a simple groupby)

and then on that new table with all my grouped data, I created the measure min date (following your instructions) and then the variable Min Date User.

So now practicaly I have two measure extra in my new table.

Now the problem is that when I try to create a relationship between cat1 (initial table1) and cat1 (new table) in order to make a simple Table (visualisation) with Table1[Cat1], Table1[Cat2], Table1[Cat3], New Table[Min Date User] it returns only the first [Min Date User] that finds in the whole Table1, and not the [Min Date User] value for every Cat1, Cat2, Cat3 sequence.