Table that filters based on max of calculated column

[PPalkowski]

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)

[harshnathani · Accepted solution]

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

[AntrikshSharma]

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.

[lbendlin]

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.

[PPalkowski]

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