DAX for Dynamic Slicing TOP N by GROUP

[Anonymous]

I am struggling with a DAX measure and was hoping to get some help.

I have an Application table with Application Number, Status Change and Date/Time of Status Change:

ApplicationNumber	Status	Status DateTime
10000001	Application Received	1/11/2019 14:03
10000001	Under Review	12/11/2019 14:27
10000001	Pending Approval	14/11/2019 14:39
10000001	Approved	18/11/2019 14:53
10000002	Application Received	30/11/2019 17:02
10000002	Under Review	6/12/2019 13:33
10000002	On Hold	7/12/2019 14:31
10000002	Pending Approval	9/12/2019 14:43
10000002	Approved	11/12/2019 14:43

I would like to implement a timeline slicer, and based on the time range of the slicer, retrieve a count of the number of applications based on the latest status of the application within the timeline. So if I set the timeline between 1st Nov and 30th Nov I expect to see the following:

Status	Count of Application
Approved	1
Application Received	1

Where as if I were to set the timeline between 1st Nov and 31st Dec I expect:

Status	Count of Application
Approved	2

I have found a DAX measure that almost worked but it does not respond to the timeline slicer correctly. It calculates the rank (of the time of update) for every application, retrieves the top rank (latest update), and then gets filtered by the time slicer. I would like the measure to be filtered first before the rank gets calculated and so on.

CountUpdated =
    COUNTROWS (
        FILTER (
            'Application',
            RANKX (
                CALCULATETABLE (
                    'Application',
                    ALLEXCEPT ( 'Application', 'Application'[applicationnumber] )
                ),
                'Application'[applicationdatetime],
                ,
                DESC,
                DENSE
            ) = 1
        )
    )
Any help would be much appreciated!

[amitchandak]

Try like this

Total value = Calculate(
    count(table[ApplicationNumber]),
    FILTER(   
            SUMMARIZE(
                table,
                table[ApplicationNumber],
				"max_date",
				MAX( 'table'[Status DateTime]),
                "Max_ID", max(table[ApplicationNumber])
            ),
            
                    table[ApplicationNumber]= [Max_ID] && table[Status DateTime]= ([max_date]) 
			)
                
          
    )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous · Accepted solution]

Hi AC,

"table[ApplicationNumber]= [Max_ID] && table[Status DateTime]= ([max_date]) "
DAX did not allow me to reference table[Status DateTime] following the SUMMARIZE, with the error "A single value for column 'Status Date Time' in table.. cannot be determined".

But also, I managed to arrived at this solution which seemed to work:
CountUpdated =
VAR MaxDate =
    MAX ( Application[applicationdatetime] )
VAR MinDate =
    MIN ( Application[applicationdatetime] )
VAR latestupdate =
    COUNTROWS (
        FILTER (
            'Application',
            RANKX (
                CALCULATETABLE (
                    'Application',
                    ALLEXCEPT ( 'Application', 'Application'[applicationnumber] ),
                    Application[applicationdatetime] >= MinDate
                        && Application[applicationdatetime] <= MaxDate
                ),
                'Application'[applicationdatetime],
                ,
                DESC,
                DENSE
            ) = 1
        )
    )
RETURN
    latestupdate

[amitchandak]

@Anonymous, Thanks for the update. If the solution you have posted is working. Please mark that as a solution, so that others can get benefited having a similar problem.