Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@Anonymous ,
Instead, you may add a calculated column.
Column = RELATED ( Projects[P_name] )
@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] )
Hi,
I think the approach that you are using is correct, are you facing any performance issues.
Regards,
Pavan Vanguri.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |