cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver III

## Power bi DAX findout 2nd MAX value

Hello Guys,
I have a table like

 item res a 1 a 30.98 1 b 30.98 1 c 20.34 1 d 17.7

and
my required table is like

 item res a max 2nd gap 1 a 30.98 30.98 30.98 0 1 b 30.98 30.98 30.98 0 1 c 20.34 30.98 30.98 0 1 d 17.7 30.98 30.98 0

when i calculated its hard to detect max cause both a and b have same values so the coming 2nd max result is wrong
i.e the coming result is like

 item res a max 2nd gap 1 a 30.98 30.98 20.34 10.64 1 b 30.98 30.98 20.34 10.64 1 c 20.34 30.98 20.34 10.64 1 d 17.7 30.98 20.34 10.64

1 ACCEPTED SOLUTION
Resolver III

Hello I've finally figures it out
Here's the used related DAX

VAR _max_count =
MAXX (
SUMMARIZE (
'table_name',
'table_name'[item],
'table_name'[res],
"count",
COUNTROWS ( 'table_name' )
),
[count]
)
VAR _value =
MAXX (
FILTER (
SUMMARIZE (
'table_name',
'table_name'[item],

'table_name'[res],
"@count",
COUNTROWS ( 'table_name' )
),
[@count] = _max_count
),
'table_name'[res]
)
VAR _sec_max_count =
MAXX (
FILTER (
SUMMARIZE (
'table_name',
'table_name'[item],

'table_name'[res],
"count2",
COUNTROWS ( 'table_name' )
),
'table_name'[res] <> _value &&
'table_name'[item] = EARLIER('table_name'[item])

),
[count2]
)

VAR _total =
CALCULATE (
COUNT ( 'table_name'[res] ),
FILTER (
'table_name',
'table_name'[item] = EARLIER ( 'table_name'[item] )

)
)
RETURN
DIVIDE(_sec_max_count,_total,0)
6 REPLIES 6
Resolver III
#EP_MAX_PARTYCOUNT% =
VAR _value =
MAXX (
FILTER (
SUMMARIZE (
'Z_EP_DATA',
'Z_EP_DATA'[PC_ID],
'Z_EP_DATA'[RESPONSE1],
"@count",
COUNTROWS ( 'Z_EP_DATA' )
),
'Z_EP_DATA'[PC_ID] = EARLIER('Z_EP_DATA'[PC_ID])
),
[@count]
)
VAR _total =
CALCULATE (
DISTINCTCOUNT( 'Z_EP_DATA'[MOBILE] ),
FILTER (
'Z_EP_DATA',
'Z_EP_DATA'[PC_ID] = EARLIER ( 'Z_EP_DATA'[PC_ID] )
)
)
RETURN
DIVIDE(_value,_total,0)
Resolver III

Hello I've finally figures it out
Here's the used related DAX

VAR _max_count =
MAXX (
SUMMARIZE (
'table_name',
'table_name'[item],
'table_name'[res],
"count",
COUNTROWS ( 'table_name' )
),
[count]
)
VAR _value =
MAXX (
FILTER (
SUMMARIZE (
'table_name',
'table_name'[item],

'table_name'[res],
"@count",
COUNTROWS ( 'table_name' )
),
[@count] = _max_count
),
'table_name'[res]
)
VAR _sec_max_count =
MAXX (
FILTER (
SUMMARIZE (
'table_name',
'table_name'[item],

'table_name'[res],
"count2",
COUNTROWS ( 'table_name' )
),
'table_name'[res] <> _value &&
'table_name'[item] = EARLIER('table_name'[item])

),
[count2]
)

VAR _total =
CALCULATE (
COUNT ( 'table_name'[res] ),
FILTER (
'table_name',
'table_name'[item] = EARLIER ( 'table_name'[item] )

)
)
RETURN
DIVIDE(_sec_max_count,_total,0)
Resolver III

Hello @Ahmedx thanks for responding but

my required result table should like

 item res a max 2nd gap 1 a 30.98 30.98 30.98 0 1 b 30.98 30.98 30.98 0 1 c 20.34 30.98 30.98 0 1 d 17.7 30.98 30.98 0
Super User

@BIswajit_Das Try:

``````max a 2nd =
VAR __Item = MAX('Table'[item])
VAR __Table = FILTER(ALL('Table'), [item] = __Item)
VAR __Summarized = SUMMARIZE(__Table, [item], [a], "__Count", COUNTROWS('Table'))
VAR __Max = MAXX(__Table, [a])
VAR __Result =
IF(
MAXX(FILTER(__Summarized, [a] = __Max),[__Count]) > 1,
__Max,
MAXX(FILTER(__Summarized, [a] < __Max), [a])
)
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver III

Hello @Greg_Deckler  Thanks for responding but what if i have multiple Items
i.e

 item res a max 2nd gap 1 a 30.98 30.98 30.98 0 1 b 30.98 30.98 30.98 0 1 c 20.34 30.98 30.98 0 1 d 17.7 30.98 30.98 0 2 a 55 55 45 10 2 b 45 55 45 10 3 a 35 65 35 30 3 c 65 65 35 30
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Power bi DAX findout 2nd MAX value.pbix