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

Helper I

## Rank to filter on this visual

Hi,

I want to create a top 5 rank for the below data on series. I am able to do this using RANKX and count quantity. Problem is I want to exclued "Not Specified" from my rank calculation to get below desired result. I then want to use this measure as filter on table visual to dynamically select the top 5 each month. When I use this measure

Rank Top 5=RANKX(ALL(Table[Series]),CALCULATE(COUNT(TABLE[Quantity])) it returns Top 5 including not specified and does not work as filter on page. Please help..

Filter not working

Desired Result

 Series Quantity Rank Series C 4 1 Series G 3 2 Series B 2 3 Series F 2 4 Series A 1 5

Data

 Series Quantity Series A 1 Series B 1 Series C 1 Series D 1 Series E 1 Series F 1 Series G 1 Series H 1 Not Specified 1 Series C 1 Series B 1 Series C 1 Series C 1 Series F 1 Series G 1 Series G 1 Not Specified 1
1 ACCEPTED SOLUTION
Super User

@hemann , Try like

Rank Top 5=RANKX(Filter( ALL(Table[Series]), Table[Series] <> "Not Specified"),CALCULATE(COUNT(TABLE[Quantity]))

or try TOPN

CALCULATE(COUNT(TABLE[Quantity]),TOPN(5,Filter( ALL(Table[Series]), Table[Series] <> "Not Specified"),CALCULATE(COUNT(TABLE[Quantity])) ,DESC),VALUES(Table[Series]))

2 REPLIES 2
Super User

@hemann , Try like

Rank Top 5=RANKX(Filter( ALL(Table[Series]), Table[Series] <> "Not Specified"),CALCULATE(COUNT(TABLE[Quantity]))

or try TOPN

CALCULATE(COUNT(TABLE[Quantity]),TOPN(5,Filter( ALL(Table[Series]), Table[Series] <> "Not Specified"),CALCULATE(COUNT(TABLE[Quantity])) ,DESC),VALUES(Table[Series]))

Helper I

The Rank solution for some reason did not work.

The TOPN solution worked, thanks for your help.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors