Better way using filter contexts

[Anonymous]

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

[v-chuncz-msft]

@Anonymous ,

You may refer to the measure below.

Measure =
CALCULATE (
    MAX ( Milestones[Date] ),
    ALLEXCEPT ( Milestones, Milestones[PID] )
)

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

@Anonymous ,

Instead, you may add a calculated column.

Column =
RELATED ( Projects[P_name] )

[Anonymous]

Hi,

I think the approach that you are using is correct, are you facing any performance issues.

Regards,

Pavan Vanguri.