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
kasiaw29
Resolver II
Resolver II

Find max in a group based on a condition

Hi there,

 

I'm looking to find MAX baseline revision no within my data. This is what I have used so far :

kasiaw29_0-1599754106162.png

 

 

Pretend Project column = x 

My goal is to find max baseline revision no where cus search = 1. 

My calculated column LC Max has this :

LC Max = IF(
CALCULATE(MAX('Activity History'[Baseline Revision No]),
ALLEXCEPT('Activity History',
'Activity History'[Project]))=MAX('Activity History'[Baseline Revision No]),1,0)

 

However it returns 0 all throughout. Even when I remove Cus search as a column I'm still getting 0 in LC Max. Can anyone recommend anything else in this scenario? 

 

Thanks

 
 
2 ACCEPTED SOLUTIONS

Hi @kasiaw29 ,

Based on your description, you can create this measure, put it in the visual filter and set its value as 1:

Measure = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Baseline Revision No] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] ),
        'Table'[Cus Search] = 1
    )
VAR _br =
    SELECTEDVALUE ( 'Table'[Baseline Revision No] )
RETURN
    IF ( _br = _max, 1, 0 )

BRN.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

I have had help on a spanish version of community. 

I've changed my LC Max to include following: 

 

LC Max = CALCULATE(
MAX('Activity History'[Baseline Revision No]),
FILTER(
ALLEXCEPT(
'Activity History',
'Activity History'[Project]
),
'Activity History'[CUS Search] = 1
)
)
 
 
Then I have created a custom column with this:
Max Baseline CA = IF('Activity History'[Baseline Revision No] = 'Activity History'[LC Max],1,0)
 

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@kasiaw29 , try like

Measure =
VAR __id = MAX ( 'Activity History'[Project] )
VAR __dt = CALCULATE ( MAX( 'Activity History'[Baseline Revision No] ), ALLSELECTED ( 'Table' ), 'Table'[Project] = __id )
If( 'Activity History'[Project] = __id, 'Activity History'[Baseline Revision No] = __dt ,1,0 )

@amitchandak Not sure if this is it, please have a look at another sample data. 

Highlighted row should be max baseline rev where cus search = 1 and that's the noly result I want to see.

kasiaw29_0-1599754037695.png

 

Hi @kasiaw29 ,

Based on your description, you can create this measure, put it in the visual filter and set its value as 1:

Measure = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Baseline Revision No] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] ),
        'Table'[Cus Search] = 1
    )
VAR _br =
    SELECTEDVALUE ( 'Table'[Baseline Revision No] )
RETURN
    IF ( _br = _max, 1, 0 )

BRN.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

I have had help on a spanish version of community. 

I've changed my LC Max to include following: 

 

LC Max = CALCULATE(
MAX('Activity History'[Baseline Revision No]),
FILTER(
ALLEXCEPT(
'Activity History',
'Activity History'[Project]
),
'Activity History'[CUS Search] = 1
)
)
 
 
Then I have created a custom column with this:
Max Baseline CA = IF('Activity History'[Baseline Revision No] = 'Activity History'[LC Max],1,0)
 

Hi @kasiaw29 ,

If this issue is solved, glad to hear that. You can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues.


Best Regards,
Yingjie Li

@kasiaw29 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Greg_Deckler
Super User
Super User

@kasiaw29 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.