Create a measure that displays only top 20 customer names based on another measure's value

[Anonymous]

I'm stumped in trying to create a measure that displays only the customer names based on the total number of open issues associated with the customer.

Eventually, I would like to pass this measure to a slicer to show the top 20 customers.

I have a measure that calculates the total number of customer issues that are open:

Customer Open Issues = 
COUNTROWS (
    ( FILTER ( 'CustomerIssue', 'pbi CustomerIssue'[Status] = "Open")))

I believe I have two options, using RANK or TOPN.

I have created the rank function, but I believe that would force me to use a table and I don't want to do that (I want to use a slicer).

I am not sure how I can use TOPN to show only customer names while I'm taking the TOPN of a measure. My guess was to use a Calculated Table.

Has anyone run into this scenario?

[v-yulgu-msft]

Hi @Anonymous,

TOPN returns a static calculated table as result. It cannot be dynamically changed based on slicer selection.

For your requirement, you should create a rank measure. Also, you need an extra table to list all available TopN items like below. Add this column into slicer. This table should be unrelated with your source data table..

Then, create a measure and add it to visual level filter, set its value to 1.

Check=IF([Rank]<=MAX(TOPNTable[TopN selection]),1,0)

Best regards,

Yuliana Gu

[Zubair_Muhammad · Accepted solution]

Hi

You can create a Rank measure and then use it as a visual filter to get top 20 customers