cancel
Showing results for
Did you mean:

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

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

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

10 REPLIES 10
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

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

Community Support

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.

Frequent Visitor

below is my formula for For data_filter

Community Support

Hi @sjpbi ，

please try this expression:

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

Frequent Visitor

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

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

Community Support

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.

Frequent Visitor

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

Community Support

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors