Forum Discussion
Better way using filter contexts
Hi,
I have a very simple data set which looks like the following:
PID MID Date
| 1 | 1 | 28/02/2019 |
| 1 | 2 | 8/04/2019 |
| 1 | 3 | 20/04/2019 |
| 1 | 4 | 28/05/2019 |
| 2 | 1 | 28/02/2019 |
| 2 | 2 | 26/04/2019 |
| 2 | 3 | 22/06/2019 |
| 2 | 4 | 1/07/2019 |
| 2 | 5 | 1/10/2019 |
PID represents a project, MID represents a milestone and Date represents when it was achieved.
I am trying to write a simple measure to get the most recent milestone for the project in the current filter context.
I have wrote the following which works however it feels list a very poor, inneffecient approach. Is there a combination with ALLEXCEPT or something along those lines which will perform the same action?
M =
VAR x = SELECTEDVALUE(Milestones[PID])
RETURN
CALCULATE(
MAX(Milestones[Date]),
FILTER(
ALL(Milestones),
Milestones[PID] = x
)
)
| PID | MID | Date | M |
| 1 | 1 | 28/02/2019 | 28/05/2019 |
| 1 | 2 | 8/04/2019 | 28/05/2019 |
| 1 | 3 | 20/04/2019 | 28/05/2019 |
| 1 | 4 | 28/05/2019 | 28/05/2019 |
| 2 | 1 | 28/02/2019 | 1/10/2019 |
| 2 | 2 | 26/04/2019 | 1/10/2019 |
| 2 | 3 | 22/06/2019 | 1/10/2019 |
| 2 | 4 | 1/07/2019 | 1/10/2019 |
| 2 | 5 | 1/10/2019 | 1/10/2019 |
Anonymous ,
Instead, you may add a calculated column.
Column = RELATED ( Projects[P_name] )
4 Replies
Anonymous ,
You may refer to the measure below.
Measure = CALCULATE ( MAX ( Milestones[Date] ), ALLEXCEPT ( Milestones, Milestones[PID] ) )thanks v-chuncz-msft .
If I add in another table named Projects which looks as follows and join them off PID can I still use an ALLEXCEPT to achieve the desired result if I replace Milestones[PID] with Projects[P_name]?
[Projects]
PID P_name 1 Proj A 2 Proj B New desired result
P_name
MID Date M Proj A 1 28/02/2019 28/05/2019 Proj A 2 8/04/2019 28/05/2019 Proj A 3 20/04/2019 28/05/2019 Proj A 4 28/05/2019 28/05/2019 Proj B 1 28/02/2019 1/10/2019 Proj B 2 26/04/2019 1/10/2019 Proj B 3 22/06/2019 1/10/2019 Proj B 4 1/07/2019 1/10/2019 Proj B 5 1/10/2019 1/10/2019 I understand that now the Milestones[PID] isn't there to add filter context but if I try and replace it with Projects[P_name] I get some really weird cross join results.
Thanks
Ben
Anonymous ,
Instead, you may add a calculated column.
Column = RELATED ( Projects[P_name] )