Translating the IF statement into DAX

Hi nursyeha001 

Try adding a column with the expression below.

Valid Result = 
VAR RowFirstName = 'Table'[First Name]
VAR RowLastName = 'Table'[Last Name]
VAR RowYear = 'Table'[Year]
VAR NoOfSubmissions = 
CALCULATE ( 
    COUNTROWS ( 
        FILTER ( 
            ALL ( 'Table' ),
            'Table'[First Name] = RowFirstName
            && 'Table'[Last Name] = RowLastName
            && 'Table'[Year] = RowYear
        )
    )    
)
VAR Result = 
SWITCH ( 
    TRUE(),
    NoOfSubmissions > 1 && 'Table'[Assignments] = "Amended Submission", "Y",
    NoOfSubmissions = 1, "Y",
    "N"
)
RETURN Result

Note: this expression won't work if you have 2 or more people with the same name in the same year.

Best regards,

Martyn

Hi nursyeha001 , MartynRamsden ,
This also will not work if there are more than a max of two entries per year per person. For instance if you had two "Amended Submissions". So, as MartynRamsden  points out if two people have the same name this will not work, my suggestion is that you include in your table a unique identifier (an ID), and that you also have month, day, year to figure out which is the latest submission. Or as an alternative to a full date, you could have the submissions numbered, and we would take the maximum submission per unique ID.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel