topic Re: get nth largest value based on other column in DAX Commands and Tips

get nth largest value based on other column

vamsi_tiger — Tue, 20 Jun 2023 12:18:07 GMT

i have a table as below

date	reason	duration
23/6	breakdown	5
24/6	snacks	10
22/6	breakdown	2
21/6	shutdown	50
21/6	other	1
24/6	other	2
24/6	break	5

so if the select the time slicer on the page to 21 to 24 it should filter the values in the table to that time line and aggregate the duration of that perticualar reason and sort is as per the total duration and give me the reason.
so lets say i need 2nd largest reason with respect to selected duration(eample 21-24 considering total table as above) the measure should give me "snacks" since it has 10 min
power PowerBI PBCommunity

Re: get nth largest value based on other column

tamerj1 — Tue, 20 Jun 2023 13:31:07 GMT

Hi vamsi_tiger
Please refer to attached sample file with the proposed solution

nth Largest Reason = MAXX ( TOPN ( 1, TOPN ( SELECTEDVALUE ( 'nth Largest'[n] ), SUMMARIZE ( 'Table', 'Table'[reason], "@Duration", SUM ( 'Table'[duration] ) ), [@Duration] ), [@Duration], ASC ), 'Table'[reason] )

Title = VAR n = SELECTEDVALUE ( 'nth Largest'[n] ) RETURN n & SWITCH ( n, 1, "st", 2, "nd", 3, "rd", "th" ) & " Largest Reson"

Re: get nth largest value based on other column

vamsi_tiger — Wed, 28 Jun 2023 11:37:20 GMT

This is way easy than the dax i wrote. Thanks a lot