cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
feedbackisagift
Frequent Visitor

Identifying a MAX value from other rows that are less than row value

Example.png

 

Hi, could someone help me figure out how to write a dynamic measure that looks at any remaining records when filtering and determines the max of the remaining records relative to it's own value?  I've attempted various things such as:

 

Max_Of_Other_Stages = CALCULATE(MAX(OppHistory[StageNameValue]),ALLEXCEPT(OppHistory,OppHistory[StageNameValue]))

 

This is not behaving as expected since it always returns 11.

 

This is a one step toward the true goal which is to calculate the days between those two records, each row's date vs. the max of the records that have a stage value < its own.

 

Any help will be much appreciated!

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@feedbackisagift

Add a filter to the DAX formula.

 

Max_Of_Other_Stages_In_The_Same_Opportunity =
CALCULATE (
    MAX ( OppHistory[StageNameValue] ),
    FILTER (
        ALLEXCEPT ( OppHistory, OppHistory[OpportunityID] ),
        OppHistory[StageNameValue] < MAX ( OppHistory[StageNameValue] )
    )
)

Capture.PNG

 

View solution in original post

3 REPLIES 3
Eric_Zhang
Microsoft
Microsoft

@feedbackisagift

Add a filter to the DAX formula.

 

Max_Of_Other_Stages_In_The_Same_Opportunity =
CALCULATE (
    MAX ( OppHistory[StageNameValue] ),
    FILTER (
        ALLEXCEPT ( OppHistory, OppHistory[OpportunityID] ),
        OppHistory[StageNameValue] < MAX ( OppHistory[StageNameValue] )
    )
)

Capture.PNG

 

Hi Eric,

 

I noticed that this does not respect filters that have been selected...  For example if I exclude stage name value 4, then I would expect the value for anything StagenameValue of 6 to recognize that the max of the records below it has now become the -1 value.

 

Do you know how to do this?

 

Thanks,

 

Joaquin

Thanks Eric.  I was also able to come up with a similar alternative...

 

Max_Of_Other_Stages = if(HASONEVALUE(OppHistory[StageNameValue]),
CALCULATE(MAX(OppHistory[StageNameValue]),
ALLEXCEPT(OppHistory,OppHistory[StageNameValue]),
OppHistory[StageNameValue]<values(OppHistory[StageNameValue])),
blank())

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors