Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PPalkowski
Helper II
Helper II

Table that filters based on max of calculated column

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)
1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

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

 

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Super User
Super User

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.

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

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors