cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

max function

Hi, I want to take only the value as max only if it is +2 greater than previous value.

example
lets say we have data like
1
2
3
4
 
if max is applied on this column it will give 4
but the requirement is to get 3
 
how can I achieve that?
6 REPLIES 6
Super User IV
Super User IV

@subho220 , Try a measure like

 


new value =
var _max = calculate(max(Table[Value]))
return
maxx(filter(Table, Table[Value] <_max),Table[Value])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

Hi @subho220 

Why is 3 the answer?  3 is not +2 greater than 2?

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

hi ,

So what I actually want is -

 

if you compare all the columns with offset +2 for max 1 compare 2, will be 1 since 2 is not +2 times greater then 1 since 2 didn't take the max value we will still have 1 as the Max 1 compares 3 will be 3 since 3 is +2 times greater then 1 since 3 is the max now next comparison will be done with 3, 3 compare 4 will be 3 since 4 is not +2 greater then 3

 

@PhilipTreacy  @amitchandak 

Hi @subho220 

Try this 

 

 

let
    NumberList = {1,2,3,4},
    
        res = List.Accumulate
            (
                NumberList,

                NumberList{0},
                
                (state, current) => 
                
                    if current > (state + 1) then current else state
                                    
            )
in
    res

 

 

 

or using some data in a column

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numbers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}}),
    res = List.Accumulate
            (
                #"Changed Type"[Numbers],

                #"Changed Type"[Numbers]{0},
                
                (state, current) => 
                
                    if current > (state + 1) then current else state
                                    
            )
in
    res

 

Phil 


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Can it be done in measure?

Hi, @subho220 

Not sure about the calculation logic.
For the following data ,your requirement is to get 7,right ?

Original Data   Max Result
2   2
3   2
5   5
6   5
4   5
7   7
8   7

 

Best Regards,
Community Support Team _ Eason



Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors