The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |