Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
Requirement: Two donut charts – One to show top city and second chart to show top second city.
When user select “India” from Country filter, First donut should show department of Coimbatore, since the count of Coimbatore is highest. Second chart must show department of Chennai.
We have tried with Top N filters, It doesn’t have an option to choose second top.
Any leads would be appreciated.
Thanks in Advance.
Solved! Go to Solution.
HI @Anonymous,
You can consider using measure formula to check the top n category and use it on visual level filter to filter the second one.
Measure =
VAR summary =
SUMMARIZE (
ALLSELECTED ( table ),
[Country],
[City],
"Count", COUNT ( table[Department] )
)
VAR _top =
MAXX ( summary, [Count] )
VAR _second =
MAXX ( FILTER ( summary, [Count] < _top ), [Count] )
VAR _city =
MAXX ( FILTER ( summary, [Count] = _second ), [City] )
RETURN
IF ( SELECTEDVALUE ( table[City] ) = _city, "Y", "N" )
Regards,
Xiaoxin Sheng
HI @Anonymous,
You can consider using measure formula to check the top n category and use it on visual level filter to filter the second one.
Measure =
VAR summary =
SUMMARIZE (
ALLSELECTED ( table ),
[Country],
[City],
"Count", COUNT ( table[Department] )
)
VAR _top =
MAXX ( summary, [Count] )
VAR _second =
MAXX ( FILTER ( summary, [Count] < _top ), [Count] )
VAR _city =
MAXX ( FILTER ( summary, [Count] = _second ), [City] )
RETURN
IF ( SELECTEDVALUE ( table[City] ) = _city, "Y", "N" )
Regards,
Xiaoxin Sheng
@Anonymous , Rank should help you.
Refer if this blog can help - One visual have only top1 another have only 2nd topper
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
@Anonymous ,
I think you need something like this.
Create a table TOPN with value 1 to 10 to use as filter.
Rank the Column