Forum Discussion
Final client status by grouping on selected date range
Hello all,
I have found solution to my problem. Here is the code:
CountOfMaxStatus = COUNTROWS( FILTER ( TestData; TestData[TimeStamp] = CALCULATE ( MAX ( TestData[TimeStamp] ); FILTER(ALL(TestData);TestData[Client] = EARLIER ( TestData[Client])); FILTER(ALL(TestData);TestData[TimeStamp]>=MIN(DateTimeDimension[TimeStamp])); filter(ALL(TestData);TestData[TimeStamp]<=MAX(DateTimeDimension[TimeStamp])) ) ))Here is link to the pbix file if you want to see it in the action: http://www.filedropper.com/maxstatusmeasure
Thanks AlB and v-lili6-msft for the help.
Community ChampionHi Anonymous
Try this. If I've understood what you are looking for I think it might work. Set up the matrix as you just explained. You could also use date on a slicer. This measure will not show anything for the Total as it is now because of the SELECTEDVALUE but if it works we can fix that, if necessary, later.
Measure =
COUNTROWS (
FILTER (
CALCULATETABLE (
FILTER (
'Raw LH';
CALCULATE (
MAX ( 'Raw LH'[CreatedDateTime] );
ALLEXCEPT ( 'Raw LH'; 'Raw LH'[LeadId] )
)
= 'Raw LH'[CreatedDateTime]
);
ALL ( 'Raw LH'[NewStatus] )
);
SELECTEDVALUE ( 'Raw LH'[NewStatus] ) = 'Raw LH'[NewStatus]
)
)Community ChampionAnonymous
On the measure in my previous post, the innermost FILTER already gets you a table with only the latest NewStatus per LeadID.
The rest is to make it work the way you want it on the matrix. The tricky part there is that placing NewStatus in the rows is affecting the table when you are doing your calculations and you would want it only to take effect at the end. I mean, first you want to select the latest NewStatus for each LeadID in the period without the filter on NewStatus and then, only then, you want the filter on NewStatus to act to count only the rows with the value you are interested in (Rejected, Qualified...)
Regarding the date filtering. Why don't you do it with a slicer on Date??
It is true and interesting what v-lili6-msft mentions. Your date column includes relevant time info (not just 00:00:00 as in most cases). I could not create a relationship with a standard date table, I suspect precisely because of that. It is the first time I am coming across this, so best to refer to what v-lili6-msft is suggesting. What I did was create an additional column with only the date (no time) in 'Raw LH' and create the relationship with 'Date' through that.
Thanks you AlB for the take on this challenge! As you wrote, your measure shows the last status only for the whole period, which is the reason why my approach was to start by grouping the values. I am not sure how to solve this tricky selection part though..
- AlB
Anonymous
Have you tried to use a Date slicer? It should work with that. It seems to be working for me.
AlBThe measure your provided shows the max status for the whole period. If date slicer de-selects this max interaction, it will disapear and will not get recalculated. Maybe I am bad at explaining what I want to achieve, so let me illustrate - we have a client with 3 interactions:
Client InteractionID Date OldStatus NewStatus xyz xyz1 01.dec A B xyz xyz2 03.dec B C xyz xyz3 05.dec C D If the date slicer has selected 12.01 - 12.05, the max interaction in this range is xyz3 and the respective max status = "D", therefore I need the output to be like this:
Measure Value B 0 C 0 D 1 However, now suppose I adjust date slicer to 12.01 - 12.04 The max interaction in this range is xyz2 and the respective max status = "C", therefore I need the output to look like this:
Measure Value B 0 C 1 D 0 This is what I am trying to achieve. I think that your measure is close to what I need, but I lack the knowledge to tweak it.
- AlB
Hey Anonymous what's up
I haven't yet had time to look in detail at your latest post but what is the status on this? Have you made any progress?
Hi AlB, I haven't had time to work on this recently. Should I happen to find the solution, I will definitely share it.