cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ALW Frequent Visitor
Frequent Visitor

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.

3 REPLIES 3
Super User
Super User

Re: Getting the top 1 row for each group dynamically

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ALW Frequent Visitor
Frequent Visitor

Re: Getting the top 1 row for each group dynamically

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.)
Community Support Team
Community Support Team

Re: Getting the top 1 row for each group dynamically

Hi @ALW,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 435 members 4,364 guests
Please welcome our newest community members: