topic Re: Table that filters based on max of calculated column in DAX Commands and Tips

Table that filters based on max of calculated column

PPalkowski — Wed, 08 Jul 2020 19:59:32 GMT

I'm trying to create a table where the line in my current formula ....

"Max Value", CALCULATE(MAX(CIAssignees[HadIssues]),FILTER(CIAssignees,CIAssignees[month]=EARLIER(CIAssignees[month]))))

is greater than two. Is there a way to do that?

MyTable =

CALCULATETABLE (

SUMMARIZE (

'CIAssignees',

'CIAssignees'[FN_Assigned TO],'CIAssignees'[MonthNo],

'CIAssignees'[Month],

'CIAssignees'[HadIssues],

"Max Value", CALCULATE(MAX(CIAssignees[HadIssues]),FILTER(CIAssignees,CIAssignees[month]=EARLIER(CIAssignees[month])))),

CIAssignees[HadIssues] > 0)

Re: Table that filters based on max of calculated column

lbendlin — Thu, 09 Jul 2020 23:07:05 GMT

yes, you need to add another filter of all the HadIssues > 2. And you will want to think how to handle the situation where this filter comes back empty.

Re: Table that filters based on max of calculated column

AntrikshSharma — Fri, 10 Jul 2020 03:58:52 GMT

The code seems to be incorrect, inside CALCULATE row context is destroyed but you are using EARLIER inside it, are you using EARLIER as a function that returns previous row? because it is supposed to return the same value in the previous Row context.

Re: Table that filters based on max of calculated column

Anonymous — Fri, 10 Jul 2020 05:26:36 GMT

Hi PPalkowski .

Try this

New Table = SUMMARIZE ( 'CIAssignees', 'CIAssignees'[FN_Assigned TO], 'CIAssignees'[MonthNo], 'CIAssignees'[Month], 'CIAssignees'[HadIssues], "Max Value", CALCULATE ( MAX ( CIAssignees[HadIssues] ), FILTER ( CIAssignees, CIAssignees[month] = MAX ( CIAssignees[month] ) && CIAssignees[HadIssues] > 0 ) )

Regards,

Harsh Nathani

Re: Table that filters based on max of calculated column

PPalkowski — Fri, 10 Jul 2020 12:41:14 GMT

Lets start with what I am looking for,

Each FN_Assigned TO submits several items per month, as little as one as much as 100.

Those , submissions must meet requirements, if ANY submissions for that month had an issue, it would be indicated in the HadIssue column.

What I am trying to do is create a table of ONLY FN_Assigned TOs that had at least one issue in each of the three months, Mar through May,

So from this sample source

FN Assigned To	Mon	HadIssues
Bruce	Mar	15
Bruce	Apr	3
Bruce	May	1
Clark	Mar	42
Clark	Apr	0
Clark	May	126
Hal	Mar	1
Hal	Apr	1
Hal	May	1

My preferred created table would be

FN Assigned To	Results
Bruce	3
Hal	3