DAX formula to get max value among selected items

[Anonymous]

I have a measure in my Power BI report which stands as follows:

Active Employees =

VAR

OnlyActiveEmployees = FILTER(Employee,Employee[Description] = "Active")

VAR

ActEmp = IF(ISBLANK(CALCULATE(SUM(Employee[EmpValue]),OnlyActiveEmployees)),0,CALCULATE(SUM(Employee[EmpValue]),OnlyActiveEmployees))

RETURN

IF (ISFILTERED(Date_List[MmmYYYY]) && calculate(count(Date_List[MmmYYYY])) > 1, "NA", ActEmp)

To simplify, this measure will give a value of "NA" if the user selects more than 1 month in the provided Date Slicer. I want to modify this measure so that its output is based on the maximum date being selected (when 2 or more dates are selected on the Date Slicer).

How can I do that?

[Anonymous]

I have a measure in my Power BI report which stands as follows:

Active Employees =

VAR

OnlyActiveEmployees = FILTER(Employee,Employee[Description] = "Active")

VAR

ActEmp = IF(ISBLANK(CALCULATE(SUM(Employee[EmpValue]),OnlyActiveEmployees)),0,CALCULATE(SUM(Employee[EmpValue]),OnlyActiveEmployees))

RETURN

IF (ISFILTERED(Date_List[MmmYYYY]) && calculate(count(Date_List[MmmYYYY])) > 1, "NA", ActEmp)

To simplify, this measure will give a value of "NA" if the user selects more than 1 month in the provided Date Slicer. I want to modify this measure so that its output is based on the maximum date being selected (when 2 or more dates are selected on the Date Slicer).

How can I do that?

[Anonymous · Accepted solution]

// This works on condition that Date_List is
// a Date table marked as such in the model.

Active Employees =
	CALCULATE(
	    SUM( Employee[EmpValue] ),
	    KEEPFILTERS( Employee[Description] = "active" )
	    LASTDATE( Date_List[Date] )
	) + 0
	
// If you don't have dates but only months,
// then you should store something like
// MonthID in your Date_List table and this
// field should be hidden and also monotonically
// increasing. Then you can write:

Active Employees =
	CALCULATE(
	    SUM( Employee[EmpValue] ),
	    KEEPFILTERS( Employee[Description] = "active" )
	    TREATAS(
	    	{MAX( Date_List[MonthID] )},
	    	Date_List[MonthID]
	    ),
	    ALL( Date_List )
	) + 0

Best

D

[Anonymous]

Thanks. Exactly what I was looking for!

[AntrikshSharma]

As suggested by @lbendlin  use a function that ignores filter context and I think you could improve your formula a bit and modify it to this, but this is my assumption that it might work for you.

Active Employees =
VAR OnlyActiveEmployees =
    FILTER (
        ALLSELECTED ( Employee[Description] ),
        Employee[Description] = "Active"
    )
VAR ActiveTotal =
    CALCULATE ( SUM ( Employee[EmpValue] ), OnlyActiveEmployees )
RETURN
    IF ( DISTINCTCOUNT ( Date_List[MmmYYYY] ) > 1, "NA", ActiveTotal + 0 )

[Anonymous]

I still get an "NA" as per your solution when the user selects more than 1 month.
I guess the issue is with this part of your formula:

IF ( DISTINCTCOUNT ( Date_List[MmmYYYY] ) > 1, "NA", ActiveTotal + 0 )

[AntrikshSharma]

Because that's what your original measure did, it is unclear what you are trying to do, can you please share the pbix file.

[Anonymous]

Thanks for your reply. Unfortunately I can't share the pbix file as it is a corporate document.
Basically, I would like to modify the existing measure (remove that "NA" part) so that when the user is selecting more than one date on the slicer (say, user selects June 2019, July 2019 and August 2019), the measure will output the value for August 2019 (which is the highest date among the 3 dates being selected.). So, if the user selects 2 dates (or more), the measure will output the value of the highest of these 2 dates (or more).

[lbendlin]

Are you sure you want to do that? Makes for a bad user experience.

[Anonymous]

I understand your point. The thing here is that Active employees refer to the number of active employees at a particular point in time and cannot be cummulative. That Date slicer controls other metrics on the Report and these metrics need cummulative figures (where the user is selecting more than 1 month on the Date slicer).

[harshnathani]

Hi @Anonymous ,

See if this video helps to get a snapshot of your active employees for a selected time period.

https://www.youtube.com/watch?v=rsx43g7TBBs

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[lbendlin]

Your first variable does not break out of the filter context. It will always only return one row. Use ALL or ALLSELECTED.