Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help with a measure


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

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support

    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

  • Anonymous's avatar
    Anonymous
    Not applicable

     

    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 ValueDescriptionIndex
    IFCIssued For Construction9
    IFRIssued For Review15

     

    Forecast Table

    EWPIssue PurposeSEPARABLE_PORTIONForecast_DateITR_NameMax rankMilestone Codes
    S-XXXIFRPhase 208-Aug-19S-201-IFR215
    S-XXXIFCPhase 208-Aug-19S-201-IFC29
    S-YYYIFRPhase 215-Aug-19S-202-IFR215
    S-YYYIFCPhase 215-Aug-19S-202-IFC29
    S-ZZZIFRPhase 222-Aug-19S-203-IFR215
    S-ZZZIFCPhase 222-Aug-19S-203-IFC29
    • Anonymous's avatar
      Anonymous
      Not applicable

      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.