Getting the top 1 row for each group dynamically

[Anonymous]

Hi, I'm trying to get count of top 1 records which are active , filtered by date range for each group. For example, if filtered by 31/07/2018 , measure should count active top 1 records

• top record for group A should be 2 and it's active
• top record for group B should be 2 but it's not avtive
• top record for group C should be 3 and it's active

Row_Number	Group	Event_Date	IsActive
1	A	1/08/2018	1
2	A	2/06/2018	1
3	A	25/05/2018	0
4	A	4/04/2018	0
5	A	13/03/2018	0
1	B	6/08/2018	1
2	B	17/03/2018	0
3	B	28/02/2018	1
4	B	9/02/2018	1
1	C	15/08/2018	1
2	C	12/08/2018	1
3	C	17/07/2018	1

 Also this measure needs to be reported by Month as well, so needs to get the all values before what ever the ending date. This is what I came up with, but when filtered by month it excludes the last month events

=
CALCULATE (
    COUNT ( 'Table'[Row_Number] ),
    FILTER (
        ADDCOLUMNS (
            'Table',
            "MaxRecord", CALCULATE (
                MAX ( 'Table'[Row_Number] ),
                FILTER ( 'Table', 'Table'[Group] = EARLIER ( 'Table'[Group] ) ),
                FILTER (
                    ALL ( 'Table'[Event_Date] ),
                    'Table'[Event_Date] <= MAX ( 'Date'[DateId] )
                ),
                ALL ( 'Date' )
            )
        ),
        [MaxRecord] = 'Table'[Row_Number]
            && 'Table'[IsActive] = 1
    )
)

Any help would be appreciated.

[Greg_Deckler]

So, what makes a record a "top" record?

[Anonymous]

Hi Greg,
Maximum Row_Number with IsActive =1 for each group counts in this measure. For the given example, count should be 2. (1 for group A and 1 for group C, group B doesn’t have a max Row_Number with IsActive =1 for the given date range.)

[v-shex-msft]

Hi @Anonymous,

You can try to use below measure to calculate active row count of filtered max row of each group:

Max Actived Row count =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Table1 ),
            [Group],
            "MaxRow", MAX ( Table1[Row_Number] )
        ),
        "IsActive", LOOKUPVALUE (
            Table1[IsActive],
            Table1[Group], [Group],
            Table1[Row_Number], [MaxRow]
        )
    )
RETURN
    COUNTROWS ( FILTER ( summary, [IsActive] = 1 ) )

Regards,
Xiaoxin Sheng