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!

[v-yangliu-msft]

Hi  @PowerBI123456  ,

Here are the steps you can follow：

1. Enter power query through transform data and select add column --- index column --- from 1 to generate the index

2. Create calculated column.

Flag =
var _1=
CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[Activity]="Request"&&[Account]=EARLIER('Table'[Account])))
var _2=
CALCULATE(MAX('Table'[Activity]),FILTER('Table',[Account]=EARLIER('Table'[Account])&&[Index]=EARLIER('Table'[Index])+1))
return
IF(
    _1=[Index]&&_2="Response",1,0)

3. Create measure.

count = SUMX(ALL('Table'),[Flag])

4. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Here is one way to do it that returns 4 from your example table.

Accounts with Response and Prev Request =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER (
                Activity,
                Activity[Activity] = "Response"
            ),
            Activity[Account],
            "cMax", MAX ( Activity[Activity Date] )
        ),
        VAR vcMax = [cMax]
        RETURN
            NOT (
                ISBLANK (
                    CALCULATE (
                        COUNTROWS ( Activity ),
                        Activity[Activity Date] < vcMax,
                        Activity[Activity] = "Request"
                    )
                )
            )
    )
)

Pat

[PowerBI123456]

@TomMartens  I have uploaded the PBI in the link below. The table on the left is what the data looks like and the table on the right is what the measure shows me. I want to do a count of that so it would be 4 in this example. 

Sample File 

[TomMartens]

Hey @PowerBI123456 ,

the most simple form to count if a measure returns a value, no matter of the result, is using the table iterator function COUNTX ().
The following measure iterates across the accounts, and counts the "Accounts" that return a value. As VALUES() returs a table (many rows, but just one column) with distinct values, no "double-counting" is happening.

Measure = 
COUNTX(
    VALUES(
        'Activity'[Account]
    )
    , [Max Request ID]
)

A little screenshot based on the pbix you provided:

Hopefully, this is what you are looking for,

Regards,

Tom

[PowerBI123456]

@TomMartens  Thank you sooo much! 

So another thing I am trying to do is a treemap showing the count by the user who made the last request, but this count is including all users who made a request on the account. Do you know how to only show the 4 users? Updated file: Sample File 

Thank you so much for your help!

[TomMartens]

Hey @PowerBI123456 , explain what should be counted based on your sample data in the pbix and the expected result.

Regards,

Tom

[PowerBI123456]

@TomMartens Hi - I am trying to show the users who put in the last request. Based on the data, it would be:

• Account 1: John
• Account 2: Will
• Account 3: Amber
• Account 4: John

I want to use a treemap to show that each of those 4 people had 1 request. The problem is my current treemap is counting everyone that made a request for each account, so its showing more than I want. Hope that makes sense. 

[TomMartens]

Hey @PowerBI123456 ,

this measure

MaxReq = 
var t = 
    ADDCOLUMNS(
        FILTER(
            'Activity'
            , CALCULATE(
                CONTAINSROW(
                    VALUES( 'Activity'[Activity] ) 
                    , "Response" 
                ) 
                , ALLEXCEPT( 'Activity' , Activity[Account] )
            )
        )
        , "MaxRequestID"
            , var account = [Account] 
            var maxID =
                CALCULATE( 
                    MAX( Activity[ID] ) 
                    , ALL( 'Activity' )
                    , 'Activity'[Account] = account
                    , 'Activity'[Activity] = "Request"
                )
            return
            maxID
    )
return

SUMX(
    t 
    , IF(
        'Activity'[ID] = [MaxRequestID] && 'Activity'[Activity] = "Request"
        , 1 --[MaxRequestID]
        , BLANK()
    )
)

allows to create this report page

From a data visualization point of view I favor the stacked bar much more then the Tree map.

If "just" want to show the user who is in charge for the last request, I would use a simple table Account | User | the measure [MaxReQ]
I'm not sure if I really understand the date stuff from your measure, maybe you have to check my measure with a larger dataset.

Hopefully, this is what you are looking for.

Regards,
Tom

[PowerBI123456]

Thank you soo much!!! This works, but the only thing it is getting the max ID of the overall request per account, but I need the Max ID of the request right before the response. I updated the sample file to show what I mean and apologies for not adding it earlier. There could be another request after a response which this measure would catch. I need the request right before the response. I added another request for account 4 after the response by Jack. This measure is catching that request when it should be John. Hope that makes sense. 

Updated file:  Sample File 

[TomMartens]

Hey @PowerBI123456 , to rephrase this requirement a little diffrently ...
You are looking for the MAX Request-ID that is smaller then the LARGEST Response-ID for the same account, correct?
Regards,
Tom

[PowerBI123456]

@TomMartens Yup! Sorry, I didnt clarify better!

[TomMartens]

Hey @PowerBI123456 ,

no problem, sometimes it takes some time to find a mutual understanding. I use "rephrasing" not because my explanation is better, it's helps to narrow down the requirement.
Nevertheless, here you go:

MaxReq = 
var t = 
    ADDCOLUMNS(
        ADDCOLUMNS(
            FILTER(
                'Activity'
                , CALCULATE(
                    CONTAINSROW(
                        VALUES( 'Activity'[Activity] ) 
                        , "Response" 
                    ) 
                    , ALLEXCEPT( 'Activity' , Activity[Account] )
                )
            )
            , "MaxResponseID"
                , var account = [Account] 
                var maxID =
                CALCULATE( 
                    MAX( Activity[ID] ) 
                    , ALL( 'Activity' )
                    , 'Activity'[Account] = account
                    , 'Activity'[Activity] = "Response"
                )
            return
            maxID
        )
        , "MaxRequestID"
            , var account = [Account]
            var maxResponseID = [MaxResponseID] 
            var maxID =
                CALCULATE( 
                    MAX( Activity[ID] ) 
                    , ALL( 'Activity' )
                    , 'Activity'[Account] = account
                    , 'Activity'[Activity] = "Request"
                    , 'Activity'[ID] < maxResponseID
                )
            return
            maxID
    )
return

SUMX(
    t 
    , IF(
        'Activity'[ID] = [MaxRequestID] && 'Activity'[Activity] = "Request"
        , 1 --[MaxRequestID]
        , BLANK()
    )
)

I added a MaxResponseID in the virtual table, that then will be used to find the MaxRequestID that is smaller than the MaxResponseID.
Hopefully, this is what you are looking for.

Regards,
Tom

[PowerBI123456]

@TomMartens  Unfortunately it timed out saying I dont have enough memory. Its probably becauue I am dealing with 20 million rows. 

[TomMartens]

Hey @PowerBI123456 ,

I'm sorry to hear that.
The "final" measure, seems not that different but there is a subtlety, it's in determinig the MaxRequestID, as it now has to find the appropriate ids first (less than the MaxResponseID) before the MaxRequestID can be determined.
This leads to materialization of "queries" as a simple boolean scan is no longer sufficient.
Next to that you just have a "dreaded" one table solution, this is responsible for this part of the measure

...
FILTER(
                'Activity'
                , CALCULATE(
                    CONTAINSROW(
                        VALUES( 'Activity'[Activity] ) 
                        , "Response" 
                    ) 
                    , ALLEXCEPT( 'Activity' , Activity[Account] )
                )
            )
...

the above determines better omits the Accounts that have no respons. This and some other components can be simplified using a proper star schema.
I recommend reading this article (Design a data model in Power BI  - Learn | Microsoft Docs).
Of course the measure can be optimized, but this can become very time consuming and a thorough understanding of your data and and your data model is necessary.
Without having access to the pbix, meaning your data, this will be impossible. Please be aware that personally I will not work with pbix files that are shared with me via private links, or private messages.
I'm afraid that this will exceed the time most of us are able/willing to spent, at least mine.

Regards,

Tom

[PowerBI123456]

@TomMartens  Thanks, and totally get it! Thanks for also sending that article. I am going to attempt this one more time since I think my model is in a star schema. Unfortunately, I can't upload the actual model since its confidential. But I have updated the sample file to include the dimension tables I do have in my model. This is obviously a lot more simple since my dimension tables have more columns and my fact table has millions of rows, but it should still work. Hopefully these dimension tables can help improve the measure you created if you don't mind looking one more time.  I truly appreciate all your help on this! 

SAMPLE FILE 

[TomMartens]

Hey @PowerBI123456 ,

this is look like a perfect star schema 🙂
Maybe you want to upload another pbix  and correct the typo in the activity table
Reponse --> Response

It is never a good idea to simplify your request by providing sample data that does not reflect your data model.

I spent some hours to come up with the "final" measure, in vain.
Silly me, that I did not ask if this is really your data model, but I was intrigued by the complexities of your question and got carried away 🙂
Nevertheless, whenever we will meet in real life you owe me a beer 😉

Regards,

Tom

[PowerBI123456]

@TomMartens Thanks, and updated file. Does that mean you will try helping me one more time 🙂

Sample File 

Apologies and lesson learned, I will make sure to upload my whole model. Definitely owe you more than 1 beer!

[TomMartens]

Yes, I will but, unfortunately I will look at this on the coming weekend, as my mind got distracted by some work related challenges.

Regards,
Tom 

[PowerBI123456]

@TomMartens No problem. Thank you, thank you, thank you!

[TomMartens]

Hey @PowerBI123456 ,

a question to the nature of the ID in your fact table.
Is this ID an index that creates a sequence growing by 1 without gaps in your fact table
If so, is this true: inside a group of events (defined by an account) the index of an response minus 1 always points to an event of type request?

Or can it be possible that the immediate predecessor of an response is another response.?

Regards,
Tom