How to filter table based on rank measure?

[sjpbi]

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()

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

Hope someone will help. Thanks

[v-tianyich-msft]

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]

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

I'm still getting all 0's

[v-tianyich-msft]

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.

[sjpbi]

below is my formula for For data_filter 

[v-tianyich-msft]

Hi @sjpbi ，

please try this expression:

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

[sjpbi]

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]

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]

Hi @sjpbi ,

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

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.

[sjpbi]

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

[v-tianyich-msft]

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:

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.