Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Better way using filter contexts

Hi,

 

I have a very simple data set which looks like the following:

 

PID          MID         Date

1128/02/2019
128/04/2019
1320/04/2019
1428/05/2019
2128/02/2019
2226/04/2019
2322/06/2019
241/07/2019
251/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
    )
)

 

PIDMIDDateM
1128/02/201928/05/2019
128/04/201928/05/2019
1320/04/201928/05/2019
1428/05/201928/05/2019
2128/02/20191/10/2019
2226/04/20191/10/2019
2322/06/20191/10/2019
241/07/20191/10/2019
251/10/20191/10/2019

 

 

1 ACCEPTED SOLUTION

@Anonymous ,

 

Instead, you may add a calculated column.

Column =
RELATED ( Projects[P_name] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may refer to the measure below.

Measure =
CALCULATE (
    MAX ( Milestones[Date] ),
    ALLEXCEPT ( Milestones, Milestones[PID] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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]

PIDP_name
1Proj A
2Proj B

 

 

New desired result

P_name

MIDDateM
Proj A128/02/201928/05/2019
Proj A28/04/201928/05/2019
Proj A320/04/201928/05/2019
Proj A428/05/201928/05/2019
Proj B128/02/20191/10/2019
Proj B226/04/20191/10/2019
Proj B322/06/20191/10/2019
Proj B41/07/20191/10/2019
Proj B51/10/20191/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] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,

 

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

 

Regards,

Pavan Vanguri.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.