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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.