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 ChampionHey Anonymous
For being a "fairly new Power BI user" you have here a pretty scary-looking, big-ass measure, dude :smileywink:
I haven't had much time but a couple of things stand out:
1. Although what you do seems logical overall, I believe the code could be done considerably leaner.
2. What is the point of the CALCULATETABLE() as first argument of SUMMARIZE? Why not just 'Raw LH'?
3. What is the point of the GROUPBY? By using it the result of the code you show above will just be a list of Ids
4. How exactly do you go about setting up the matrix with the measure? The issue might be there. (If you can provide the actual Power BI file including the reports rather than just the excel with, it would be useful)
5. The outermost FILTER(), does it not yield an error at execution time? You are passing on a table as filter argument.
Hopefully tomorrow I will have some more time.
Best
Hi again,
v-lili6-msftthanks for attempt, but these solutions do not fix the issue.
AlBwith regards to your questions:
1. I agree that code which I provided is bloated, because I am trying to stitch together measure that is doing its job.
2. Reason for doing CALCULATETABLE() is to filter the selected date range only
3. GROUPBY() was my attempt to isolate unique eventId' s
4. I see what you mean, and this actually might be the root of the problem, please see below.
I tried to simplify the task, and ignore the relative date filtering for a while. I created a new table which just lists the unique status change Ids for the whole period, and then created a connection with the main datatable:
Testing =
SELECTCOLUMNS (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
ExampleData,
ExampleData[LeadId],
ExampleData[Id],
ExampleData[CreatedDateTime]
),
"MaxDate", CALCULATE (
MAX ( ExampleData[CreatedDateTime] ),
FILTER ( ExampleData, ExampleData[LeadId] = EARLIER ( ExampleData[LeadId] ) )
)
),
ExampleData[CreatedDateTime] = [MaxDate]
),
"Id", ExampleData[Id]
)I then created a measure for exactly the same code as above, but wrapped it in the COUNTROWS(). I then created the table of statuses and inserted both the measure and count of Testing[Id] as values. The output for both variants of the same code are different.
I don't understand how can this be, because the "Count of Id" is showing the correct output, but "Measure" is showing the wrong one, even though the both use exactly the same code! I feel like I am missing something very obvious here, but I don't know what. ;/
Here is link to the model: http://www.filedropper.com/example_20
- AlB
Hi 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] ) )- AlB
Anonymous
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..