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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors