Help with a measure

[Anonymous]

Hello here is my problem, i am trying to return a particular code through a measure from the table below.

at the moment code below doesn't contains "Issue Purpose" in it so it returns first value it finds. Can someone help

Measure Test =
VAR CurrentRank = SELECTEDVALUE( Forecast[Max rank] )
VAR IssuePurposeCount = DISTINCTCOUNT ( Forecast[IssuePurpose] )
RETURN
IF (
    NOT ISBLANK ( CurrentRank ),
    IF (
        CurrentRank <= IssuePurposeCount,
        LASTNONBLANK (
            TOPN ( IssuePurposeCount, VALUES ( Forecast[Milestone Codes]),Forecast[Milestone Codes],ASC ),
            0
        )
    ),
Blank()
)

Issue Purpose Description Index
FYI     For Your Information    1
IAB    Issued For As-Built    3
ICH    Issued for Construction with Holds    7
ICR     Issued For Construction Record    4
IFA     Issued For Approval    5
IFB     Issued For Bid    6

[Anonymous]

hopefully table below will help, based on the EWP and Issue purpose i want to return Milstone code from the Milestones codes table

Milestones Code Table

Code Value	Description	Index
IFC	Issued For Construction	9
IFR	Issued For Review	15

Forecast Table

EWP	Issue Purpose	SEPARABLE_PORTION	Forecast_Date	ITR_Name	Max rank	Milestone Codes
S-XXX	IFR	Phase 2	08-Aug-19	S-201-IFR	2	15
S-XXX	IFC	Phase 2	08-Aug-19	S-201-IFC	2	9
S-YYY	IFR	Phase 2	15-Aug-19	S-202-IFR	2	15
S-YYY	IFC	Phase 2	15-Aug-19	S-202-IFC	2	9
S-ZZZ	IFR	Phase 2	22-Aug-19	S-203-IFR	2	15
S-ZZZ	IFC	Phase 2	22-Aug-19	S-203-IFC	2	9

[Anonymous]

I am having problems with determining which EWP and Issue purpose is currently active and how to read the value and return a code based on it. Cant think of any particular solution for this.

[v-yuta-msft]

@Anonymous ,

I haven't found the column [Max rank], [Milestone Codes] in your sample data, could you please share the complete sample data and show your expected result?

Regards,

Jimmy Tao