Forum Discussion
Counting a Measure
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!
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
27 Replies
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
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.
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
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.
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
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!
