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

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

Reply
sjpbi
Frequent Visitor

How to filter table based on rank measure?

Hi I have an issue filtering my table based on rank value, I have this scenario:

 

When the user select a sample size %, the table should filter based on the rank (data_filter measure) 

  • Total Data count = total unique ids
  • For data_filter = 15% selected by user multiply by total data count =2.7 (round up)=3
  • data_filter in table= rank based on created calculated column randomrow=rand()

 

sjpbi_0-1698216931801.png

 

My expected should be like this when user select a sample size, the table will automatically filter based on random selected %

 

sjpbi_1-1698217162207.png

 

Hope someone will help. Thanks

 

 

10 REPLIES 10
v-tianyich-msft
Community Support
Community Support

Hi @sjpbi ,

 

Try expression below:

data_filter = RANKX(ALLSELECTED('Table'[RandonRows]),CALCULATE(SUM([RandonRows])),,DESC,Dense)
For data_filter = CALCULATE(ROUND([Total Data count]*[% Value],0),ALLSELECTED('Table'[RandonRows]))
For visual = IF('Table'[data_filter]<='Table'[For data_filter],1,0)
RandonRows = RAND()
Total Data count = SUM('Table'[Data Count])
 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

sjpbi
Frequent Visitor

  • data_filter  is a measure in a table with this formula  RANKX(ALLSELECTED(tablename),sum(randomrow),,ASC)

I'm still getting all 0's

 

sjpbi_0-1698303014160.png

 

 
 

Hi @sjpbi ,

 

Please try to add All(your table) to the for_data_filter expression

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

below is my formula for For data_filter 

sjpbi_0-1698304282018.png

 

Hi @sjpbi ,

 

please try this expression:

For data_filter = CALCULATE(ROUND([Total Data count]*[% Value],0),ALL('Table'))

 

can't use ALL since i have other filters in the report.

 

  • For data_filter = ROUND([Total data count]*[%selected],0)
  • RandomRow = rand()
  • For visual = IF([data_filter]<=[For data filter],1,0)
  • data_filter = RANKX(ALLSELECTED(tablename),sum(RandomRow)],,ASC)
     
    Still unable to display 1s and 0s in For visual, so it can be filtered to 3 rows only
     sjpbi_1-1698306556116.png

     

     

 

sjpbi
Frequent Visitor

Actually the data filter column is the rank measure of random row column i created and sort out to asc the table data should filter based on the total count * %value selected by user so the table will display top 18 counts

v-tianyich-msft
Community Support
Community Support

Hi @sjpbi ,

 

Based on your description, I created simple samples and you can check the following results:

vtianyichmsft_0-1698289843874.png

 

For data_filter = ROUND([Total Data count]*[% Value],0)

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Data should filter only 3 rows  since it's 18*15%=2.7=3

Hi @sjpbi ,

 

I'm trying to create a measure for the filter to filter out columns with a value of 1,and getting the result:

vtianyichmsft_0-1698301081969.png

 

For visual = IF('Table'[data_filter]<='Table'[For data_filter],1,0)

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.