Forum Discussion

sjpbi's avatar
sjpbi
Frequent Visitor
2 years ago

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

 

 

Dashboards
Data Modeling
Reports

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      sjpbi
      Frequent Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

  • sjpbi's avatar
    sjpbi
    Frequent Visitor
    2 years ago

    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

  • sjpbi's avatar
    sjpbi
    Frequent Visitor
    2 years ago
    • data_filter  is a measure in a table with this formula  RANKX(ALLSELECTED(tablename),sum(randomrow),,ASC)

    I'm still getting all 0's

     

     

     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        sjpbi
        Frequent Visitor
        2 years ago

        below is my formula for For data_filter 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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