topic Re: MAX value with Filter or If in DAX Commands and Tips

MAX value with Filter or If

Colin_Davis — Thu, 02 Jan 2025 09:45:48 GMT

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

Re: MAX value with Filter or If

bhanu_gautam — Thu, 02 Jan 2025 09:53:09 GMT

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()
)

Re: MAX value with Filter or If

Colin_Davis — Thu, 02 Jan 2025 11:07:37 GMT

Thank you, it returns True/False, I don't know why "Blank()" isn't returned, but I can use it.
Thank you very much.