COUNTROWS but only for the last date, ignoring filter context

[GarC]

I expect this is actually quite easy but I'm new to PowerPivot/PowerBI and am struggling a little.

Below is a simplified dataset of a table named Daily. The customer numbers are the same each day and each passes through stages on the way to being "Complete". When they are complete, they stay complete and will not be removed. The project is over when all Build Flags are complete.

Run Date	City	CustomerNo.	Build Flag
01/10/2016	London	1	Not Started
01/10/2016	London	2	Not Started
01/10/2016	Paris	3	Not Started
01/10/2016	Paris	4	Not Started
01/10/2016	Paris	5	Not Started
02/10/2016	London	1	Not Started
02/10/2016	London	2	Not Started
02/10/2016	Paris	3	In Progress
02/10/2016	Paris	4	In Progress
02/10/2016	Paris	5	In Progress
03/10/2016	London	1	In Progress
03/10/2016	London	2	In Progress
03/10/2016	Paris	3	In Progress
03/10/2016	Paris	4	In QC
03/10/2016	Paris	5	In QC
04/10/2016	London	1	In Progress
04/10/2016	London	2	In QC
04/10/2016	Paris	3	In QC
04/10/2016	Paris	4	Completed
04/10/2016	Paris	5	Completed

<tbody>
</tbody>

I have various reports which work fine but am having difficulty creating a table which contains a COUNTROWS but only for the last date in the model.
I want to ensure that the date does not respond to filter context which seems to be the problem.

COUNTROWS	Not Started	In Progress	In QC	Completed
London	0	1	1	0
Paris	0	0	1	2

<tbody>
</tbody>

I feel I am very close but am inexperienced with FILTER() and ALL().

I have a few measures which I feel are not far off but none are quite right. A little guidance would be appreciated.

This is a convoluted way to just COUNTROWS and responds to filter context. I know it needs ALL() but haven't got it right yet.

CountRows_OnlyOnLastDate_v1 = CALCULATE(COUNTROWS(Daily),FILTER(Daily,Daily[run date]=LASTDATE(Daily[run date])))

This expression is returning all data. It doesn't respond to filter context (good) but isn't pre-filtering to the LASTDATE (bad).

CountRows_OnlyOnLastDate_v2 = CALCULATE(COUNTROWS(Daily),FILTER(ALL(Daily[Run Date]),Daily[Run Date]=LASTDATE(Daily[Run Date])))

This third attempt is also close. While it pulls the data for the last date, if there is no value for the last date, it looks for the next highest max date and brings that in. It also responds to filter context.

CountRows_OnlyOnLastDate_v3 = CALCULATE(COUNTROWS(Daily),FILTER(ALL(Daily[run date]),[run date]=MAX(Daily[run date])))

Any idea...

 

[Ozeroth]

Hi GarC,

I think you want a measure like this:

=
CALCULATE (
    COUNTROWS ( Daily ),
    CALCULATETABLE ( LASTDATE ( Daily[Run Date] ), ALL ( Daily ) )
)

1. CALCULATETABLE ( LASTDATE ( Daily[Run Date] ), ALL ( Daily ) ) returns the last Run Date with all filters on the Daily table removed. LASTDATE returns a 1x1 table (rather than a scalar), so this can be used as a filter argument for the outer CALCULATE.
2. The outer CALCULATE leaves all filters intact except Run Date (due to step 1 above).
3. You may want to add +0 to the end of the measure if you prefer to see zeros rather than blanks when there are zero rows.

This produced the table you were looking for in a test model at my end. Hopefully it does the trick for you!

Owen

 

[GarC]

Hi Owen. Thank you for the response. I will be home later and will try your solution.

Gar.

 

[GarC]

Hi Owen

Thank you for your solution. It worked very well with my sample set and I look forward to testing at work on Monday.

I also added an additional filter condition which other users might find useful...

Measure:= CALCULATE([CountRows],Daily[BuildFlag]="Build Complete",CALCULATETABLE(LASTDATE(Daily[run date]),ALL(Daily)))