Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SC0614
Frequent Visitor

Top N filter not working with a filtered table

Hi,

 

I have a problem when I tried to filter top 20 customers in a filtered table.

 

The table looks like as follow:

 

Capture.PNG

and it has filters as follow:

Capture1.PNG

Measures are:

rank_var = RANKX(ALLSELECTED(Cust_Barrie[CUST_NM]),[corr_var],,DESC)

top 20 corr_var = IF([rank_var] <= 20, [Difference],BLANK())

 

I want a table with only top 20 customers, but I can't filter out blank values in column "top 20 corr_var". It will return only the top 1 customer.

 

I think the reason is I am using "ALLSELECTED" instead of "ALL" in "rank_var". But if I use "ALL", it will rank for all customers in the dataset instead of filtered customers in the table.

 

Is there a way to solve this problem?

 

Thanks,

Sherry

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @SC0614

Use this measure for rank

Measure = RANKX(ALLSELECTED(Sheet12),[Measure 2],,DESC,Dense)

9.png

 

Best Regards

Maggie

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @SC0614

Use this measure for rank

Measure = RANKX(ALLSELECTED(Sheet12),[Measure 2],,DESC,Dense)

9.png

 

Best Regards

Maggie

Hi Maggie,

 

Thanks for your response. It gives me every customer's list but I still can't filter out the top 20 customers. If I filter by "Measure less than or equal to 20", it would return only the first rank.

Hi @SC0614

I can do it on my site, could you show me some screenshots for better troubleshooting?

5.png

Best Regards

Maggie 

Thanks, Maggie. Please find my screenshot below.

Capture23.PNG

Here are my measures:

 

  • corr_var = [Standard_deviation]/[avg_dep]
    • Standard_deviation =
      STDEVX.S(
      SUMMARIZE ( 'Acct', Acct[TIME_DESC], Acct[CUST_ID] ),
      CALCULATE ( SUM ( 'Acct'[DEP_BAL_2] ) )
      )
    • avg_dep = SUM(Acct[DEP_BAL_2])/2
  • Difference = [current month] - [previous month]
    • previous month = CALCULATE( SUM( 'Acct'[DEP_BAL_2] ), FILTER( Acct, 'Acct'[TIME_KEY] = MAX(Acct[TIME_KEY])-40) )
    • current month = CALCULATE( SUM( 'Acct'[DEP_BAL_2] ), FILTER( Acct, 'Acct'[TIME_KEY] = MAX(Acct[TIME_KEY])) )
  • measure = RANKX(ALLSELECTED(Acct[CUST_ID]),[corr_var],,DESC,Dense)

 

Regards,

Sherry

Hi @SC0614

Sorry, i don't find any error from your screenshot, the meaure which calculate teh rank show the correct rank from 1 to 26, not 1 for all rows.

The difference between your meaure and mine is 

  • measure = RANKX(ALLSELECTED(Acct[CUST_ID]),[corr_var],,DESC,Dense)  ->yours
  • measure = RANKX(ALLSELECTED(Acct),[corr_var],,DESC,Dense)                   ->mine

Best Regards

Maggie

Finally worked!!! Thanks a lot!!!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.