cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

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

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors