Counting a Measure

[PowerBI123456]

Updated with sample file: Sample File 

Hi, hope someone can help!

I have the data below and using the measure below to show me the ID of the most recent "Request" if there was a "Response" by account number. Note: there could be multiple IDs per 1 account number, just depends on if there was a response or not. 

Measure= CALCULATE(MAX('Activity'[ID]),Filter('Activity', 'Activity'[Activity] ="Request" && CALCULATE(MAX('Activity'[ID])<CALCULATE(MAX('Activity'[ID]),'Activity'[Activity]="Response",ALLEXCEPT('Activity',' Activity'[Account])))))

Account	Activity	Activity Date	User	ID
1	Request	01/01/2021	Tom	1
1	Request	01/02/2021	Jim	2
1	Request	01/03/2021	John	3
1	Response	01/04/2021	Stephanie	4
2	Request	01/05/2021	Will	5
2	Response	01/06/2021	Joe	6
3	Request	01/07/2021	Anna	7
3	Request	01/08/2021	Amber	8
3	Response	01/09/2021	Katie	9
4	Request	01/10/2021	Jack	10
4	Response	01/11/2021	Bob	11
4	Request	01/12/2021	John	11
4	Response	01/13/2021	Tom	12
5	Request	01/14/2021	Liam	13

So the lines highlighted in yellow is what my meausre is getting me. However, I want to take it one step further and do count of that measure. So in this case, it would be 4.  Any tips?

Thanks in advance!

[PowerBI123456]

@TomMartens Hi, its not always minus 1 since other activity can happen between a request and response. But the last request before a response received should be the matching request. So it can look something like the below, I only care about the requests and response, not the other activity. So for account 1, I am looking for Joe's request (ID 3).  Does that help? I have uploaded the data to include this if it helps. Sample File 

Thanks!

[TomMartens · Accepted solution]

Hey @PowerBI123456 ,

here you will find a new approach :-), be aware that this approach is referencing the columns from your dimension tables. For this it's also necessary to change the visuals as well. Meaning: use columns from the dimension tables instead.
This measure is also not that generic, as it returns the max requestID, so it will return the expected results whenever the acoount colum is used.
If you use the measure on a card visual it will retrun 12 instead of 4. If you need the 4 I recommend using a SUMX or COUNTX in combination with VALUES('...'[account]  

MaxReq Star = 
var t = 
ADDCOLUMNS(
        VALUES( 'DIM: Accounts'[Account] )
        , "maxrequestid" 
            , var MaxResponseID =
                CALCULATE( 
                    MAX( 'FACT: Activity'[ID] )
                    , ALL('DIM: Date' )
                    , ALL( 'DIM: Users' )
                    , ALL( 'FACT: Activity'[ID] )             
                    , 'DIM: Activity'[Activity] = "Response"
                )
            var MaxRequestID = 
                CALCULATE( 
                    MAX( 'FACT: Activity'[ID] )
                    , ALL( 'DIM: Date' )
                    , ALL( 'DIM: Users' )
                    , 'DIM: Activity'[Activity] = "Request"
                    
                    --, 'FACT: Activity'[ID] = MaxResponseID - 1
                    , 'FACT: Activity'[ID] < MaxResponseID
                )
            return
            MaxRequestID
        )
return

CALCULATE(
    MAX( 'FACT: Activity'[ID] )
    , TREATAS( t , 'DIM: Accounts'[Account] , 'FACT: Activity'[ID] )
)

Here is a screen shot that shows the column usage of the tree map visual:

Be aware that the overall challenge we are facing is based on the fact that the datastore (our beloved SSAS Tabular inside Power BI) does not know a sequence data type. Sometimes, here, this makes things hard, the other times it's a plus.

Nevertheless, if this does not work, you might want to read this article, here I present a different approach to tackle the previous value challenge: The previous value - Mincing Data - Gain Insight from Data (minceddata.info)

Regards,

Tom

[PowerBI123456]

@TomMartens Thank you soooo much!!!! You rock!

[PowerBI123456]

@TomMartens Thank you!!! I think it will work, but its still calculating. I am dealing wtih 20 million rows so its running slow. The first measure ran fast. Anyway to make this faster?

[TomMartens]

Thanks for the clarification, I will look into this the upcoming days.

I'm not sure if the current definition of the meaure "Max Request ID" is not 100% exact at least not to my understanding as this measure returns a value for all the accounts if you add the column user to the top right table visule.
From my understanding this measure should return a blank value for the Users Jim and John for Account:1

Regards,

Tom

[PowerBI123456]

@TomMartens  Thank you! Yeah, it should be blank. Its the best I could come up with. 

[TomMartens]

Hey @PowerBI123456 ,

your measure is referencing a column [Action ...] that is not in the table you provided.

Create a pbix file that contains sample data, but still reflects your data model. Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom