The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
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"
This is way easy than the dax i wrote. Thanks a lot
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"
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |