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.
<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.
<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.
This expression is returning all data. It doesn't respond to filter context (good) but isn't pre-filtering to the LASTDATE (bad).
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.
Any idea...
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.
Code:
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).
Code:
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.
Code:
CountRows_OnlyOnLastDate_v3 = CALCULATE(COUNTROWS(Daily),FILTER(ALL(Daily[run date]),[run date]=MAX(Daily[run date])))
Any idea...