Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Getting the top 1 row for each group dynamically

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_NumberGroupEvent_DateIsActive
1A1/08/20181
2A2/06/20181
3A25/05/20180
4A4/04/20180
5A13/03/20180
1B6/08/20181
2B17/03/20180
3B28/02/20181
4B9/02/20181
1C15/08/20181
2C12/08/20181
3C17/07/20181

 

 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.

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      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.)
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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