Forum Discussion
Summarize by one column ignoring other columns
- 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]
)
)
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]
)
)