cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Translating the IF statement into DAX

I have an easy-sounding issue, in theory but am trying to translate this into DAX logic.
I'm trying to translate this logic using a nested IF statement where, it reads for every year and will take the latest version of a document. If there's only one submission, "Submission", then it will take this version. Otherwise, if there's an amended submission "Amended Submission", it will take the latest amended document submission for that year, i.e. "Amended Submission". Not all years have "Amended Submission", some years may just have a "Submission".

E.g. IF (Table[YA]=2019, IF(Document="Amended Submission", "Y","N"), IF(Table[YA]=2018, IF (Document="Amended Submission","Y","N")))...

Any help would be great!

Thanks!
Nur
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

Re: 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

Re: Translating the IF statement into DAX

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Translating the IF statement into DAX

Hi @nursyeha001 ,
Your example does not seem to follow your written example. ATR? is not mentioned in the written portion. I also cannot tell if you have multiple tables and the relationships.

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

Re: Translating the IF statement into DAX

Hi Nathaniel,

 

It's all in one table that I'm importing into Power BI and need to transform using DAX to create a column, Valid Record, that for each person, looks at the Assignments submitted for the Year and takes the latest version indicated with  Y, any earlier versions will be indicated as a N.

 

Sample Data:

First NameLast NameYearAssignmentsValid Record
PeterParker2015SubmissionN
PeterParker2015Amended SubmissionY
TonyStark2017SubmissionY
BruceWayne2018SubmissionN
BruceWayne2018Amended SubmissionY

 

Thanks!

Nur

Highlighted
Super User I
Super User I

Re: 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

Re: Translating the IF statement into DAX

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors