Summarize by one column ignoring other columns

[JIGAR]

Hello, 

I need some help with restructing one of my measures. I am trying to summarize a table by a particular column and calculating the MIN datetime, but I would like to ignore other column. Here is the example of what I am trying to do.

Actual Data 

Level	Customer	Join Date	filterRecords
Basic	A	1/31/2022 10:15	1
Premium	A	1/31/2022 10:35	1
Basic	B	1/31/2022 10:55	1
Basic	B	1/31/2022 10:25	1
Premium	B	1/31/2022 11:35	1
Basic	A	1/31/2022 10:15	0
Premium	A	1/31/2022 10:35	0
Basic	C	1/31/2022 10:15	1
Premium	C	1/30/2022 10:35	1
Basic	D	1/31/2022 10:55	1
Basic	D	1/31/2022 10:25	1
Premium	D	1/28/2022 11:35	1
Basic	C	1/31/2022 10:15	0
Premium	C	1/31/2022 10:35	0
Basic	E	1/31/2022 10:55	1
Basic	E	1/31/2022 10:25	1
Premium	E	1/21/2022 11:35	1

This is a subset of how my data looks like. In this (filterRecords) is one of the measure that I have created based on certain criteria. 

Below is my desired result

Basic	2
Premium	3
Total	5

I want to calculate total number of customers based on the condition where filterRecords= 1 and then I need to count the customers based on their minimum join date MIN (joinDate). Currently my measure is returning two records for customer A as there are 2 Levels against customer A.

I would like the measure to return only one record for customer A based on the MIN(joinDate). 

Here is the measure I have tried.

customerCount =
var tempTable1 =
CALCULATETABLE(
Table,
FILTER( Table, Table[_filterRecords] = 1 ))
var tempTable2 =
SUMMARIZE(tempTable1,Table[Customer],"Min Date", CALCULATE(MIN(Table[JoinDate]), ALLEXCEPT(Table, Table[Customer])))
RETURN
COUNTROWS(tempTable2)

Can someone please help me with this measure ? I am not sure what I am doing wrong here.

Any help on this would be greatly appreciated.

Thank you

Regards

[Anonymous]

Hi @JIGAR 

When you summarize your temp table on Customer, there is no context of Level...I did add a Calculated column instead

Check = 
VAR T1=FILTER(ALL('Table'),'Table'[filterRecords]=1&&[Customer]=EARLIER('Table'[Customer]))
VAR T2=GROUPBY(T1,[Customer],"minDate",MINX(CURRENTGROUP(),[Join Date]))
RETURN 
IF([Join Date]=MAXX(T2,[minDate]),1,0)

customerCount = CALCULATE(COUNTROWS(VALUES('Table'[Customer])),'Table'[Check]=1)

[lbendlin · Accepted solution]

You were pretty close.

customerCount =
VAR c =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[Level] ),
        'Table'[filterRecords] = 1
    ) -- only consider valid records 
VAR j =
    ADDCOLUMNS (
        c,
        "min join",
            VAR d = [Customer]
            RETURN
                CALCULATE ( MIN ( 'Table'[Join Date] ), ALL ( 'Table' ), 'Table'[Customer] = d )
    ) -- calculate earliest join date 
VAR f =
    FILTER ( j, [Join Date] = [min join] ) -- only consider rows for min join date 
RETURN
    COUNTROWS ( f )

or all in one: 

customerCount =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                'Table',
                ALLEXCEPT ( 'Table', 'Table'[Level] ),
                'Table'[filterRecords] = 1
            ),
            "min join",
                VAR d = [Customer]
                RETURN
                    CALCULATE ( MIN ( 'Table'[Join Date] ), ALL ( 'Table' ), 'Table'[Customer] = d )
        ),
        [Join Date] = [min join]
    )
)