Forum Discussion

JIGAR's avatar
JIGAR
Icon for Resolver IV rankResolver IV
4 years ago
Solved

Summarize by one column ignoring other columns

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. He...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    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]
        )
    )

     

     

     

      

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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)