Forum Discussion
MAX value with Filter or If
I want to create a New Column that contains True, if this is the higest version for this user, for this course, and the status is completed. I have the following table called Main:
| User Name | Course Title | Status | Version | Highest Version |
| Fred Smith | Excel for Beginners | In Progress | 6.0 | |
| Fred Smith | Excel for Beginners | Completed | 5.0 | True |
| Fred Smith | Excel for Beginners | Completed | 4.0 | |
| Fred Smith | Advanced Excel | Completed | 3.0 | True |
| Fred Smith | Advanced Excel | Completed | 2.0 | |
| Fred Smith | Advanced Excel | Completed | 2.0 | |
| Jane Doe | Excel for Beginners | Completed | 5.0 | True |
| Jane Doe | Excel for Beginners | Completed | 4.0 |
I want to create the "Highest Version" column that will show the row that is the highest version of each course completed by each user.
Thank you.
Colin
Colin_Davis , Create a new column using
DAX
Highest Version =
VAR CurrentUser = 'Main'[User Name]
VAR CurrentCourse = 'Main'[Course Title]
VAR CurrentVersion = 'Main'[Version]
RETURN
IF(
'Main'[Status] = "Completed" &&
CurrentVersion =
CALCULATE(
MAX('Main'[Version]),
FILTER(
'Main',
'Main'[User Name] = CurrentUser &&
'Main'[Course Title] = CurrentCourse &&
'Main'[Status] = "Completed"
)
),
TRUE,
BLANK()
)
2 Replies
- bhanu_gautam
Super User
Colin_Davis , Create a new column using
DAX
Highest Version =
VAR CurrentUser = 'Main'[User Name]
VAR CurrentCourse = 'Main'[Course Title]
VAR CurrentVersion = 'Main'[Version]
RETURN
IF(
'Main'[Status] = "Completed" &&
CurrentVersion =
CALCULATE(
MAX('Main'[Version]),
FILTER(
'Main',
'Main'[User Name] = CurrentUser &&
'Main'[Course Title] = CurrentCourse &&
'Main'[Status] = "Completed"
)
),
TRUE,
BLANK()
)Thank you, it returns True/False, I don't know why "Blank()" isn't returned, but I can use it.
Thank you very much.
