Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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!
Hey @PowerBI123456 , explain what should be counted based on your sample data in the pbix and the expected result.
Regards,
Tom
@TomMartens Hi - I am trying to show the users who put in the last request. Based on the data, it would be:
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.
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
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
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
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
@TomMartens Unfortunately it timed out saying I dont have enough memory. Its probably becauue I am dealing with 20 million rows.
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
@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!
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
@TomMartens Thanks, and updated file. Does that mean you will try helping me one more time 🙂
Apologies and lesson learned, I will make sure to upload my whole model. Definitely owe you more than 1 beer!
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
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