Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.