topic Re: DAX formula to get max value among selected items in DAX Commands and Tips

DAX formula to get max value among selected items

Anonymous — Fri, 03 Jul 2020 19:26:00 GMT

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?

Re: DAX formula to get max value among selected items

lbendlin — Fri, 03 Jul 2020 21:30:52 GMT

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

Re: DAX formula to get max value among selected items

AntrikshSharma — Sat, 04 Jul 2020 09:53:22 GMT

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 )

Re: DAX formula to get max value among selected items

Anonymous — Sat, 04 Jul 2020 15:58:24 GMT

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 )

Re: DAX formula to get max value among selected items

AntrikshSharma — Sat, 04 Jul 2020 16:19:03 GMT

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

Re: DAX formula to get max value among selected items

Anonymous — Sat, 04 Jul 2020 17:45:54 GMT

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

Re: DAX formula to get max value among selected items

lbendlin — Sat, 04 Jul 2020 19:26:03 GMT

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

Re: DAX formula to get max value among selected items

Anonymous — Mon, 06 Jul 2020 06:47:08 GMT

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

Re: DAX formula to get max value among selected items

Anonymous — Mon, 06 Jul 2020 10:03:09 GMT

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!

Re: DAX formula to get max value among selected items

Anonymous — Mon, 06 Jul 2020 16:09:49 GMT

// 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

Re: DAX formula to get max value among selected items

Anonymous — Mon, 06 Jul 2020 18:27:19 GMT

Thanks. Exactly what I was looking for!