Forum Discussion

AbhishekAejae's avatar
AbhishekAejae
Regular Visitor
2 years ago
Solved

Help needed regarding Nested Filter in Power BI

I am looking for help with nested filtering in Power BI and below is my situation,

 

I have a country column and Device name column in my dataset. I want to first filter out the countries having greater than 10 devices count and among these filtered countries, I want to then filter out bottom 20 countries which should be dynamic and should be affected with the slicers placed.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, AbhishekAejae 

    Thanks for rajendraongole1 reply. You can try the following measure to achieve your need.

    Counts =
VAR __countsCountry =
    CALCULATE (
        COUNT ( 'Table'[Country] ),
        ALLEXCEPT ( 'Table', 'Table'[Country] )
    )
RETURN
    __countsCountry

Flag = 
IF ( [Counts] > 3, 1 )

Rank = 
VAR __rank =
    RANKX ( FILTER ( ALLSELECTED ( 'Table' ), [Flag] = 1 ), [Counts],, ASC, DENSE )
RETURN
    __rank


    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

    • AbhishekAejae's avatar
      AbhishekAejae
      Regular Visitor
      1 year ago

      Hi rajendraongole1 - Below is the Sample data for my scenario in which I have 17 countries and devices assigned to them respectively. For eg: Switzerland has 37 device count and similarly I want to filter all countries having greater than 10 device count and again filter bottom 5 countries from the first filtered countries.

       

      Device NameCountry
      PHINHY-L78000Switzerland
      PHINHY-L78001Belgium
      PHINHY-L78002Switzerland
      PHINHY-L78003Austria
      PHINHY-L78004Slovenia
      PHINHY-L78005Switzerland
      PHINHY-L78006Slovenia
      PHINHY-L78007Slovenia
      PHINHY-L78008Austria
      PHINHY-L78009Switzerland
      PHINHY-L78010Switzerland
      PHINHY-L78011Slovenia
      PHINHY-L78012Austria
      PHINHY-L78013Germany
      PHINHY-L78014Belgium
      PHINHY-L78015Switzerland
      PHINHY-L78016Switzerland
      PHINHY-L78017Uzbekistan
      PHINHY-L78018Switzerland
      PHINHY-L78019Switzerland
      PHINHY-L78020Switzerland
      PHINHY-L78021Uzbekistan
      PHINHY-L78022Switzerland
      PHINHY-L78023Poland
      PHINHY-L78024Austria
      PHINHY-L78025Switzerland
      PHINHY-L78026Switzerland
      PHINHY-L78027Uzbekistan
      PHINHY-L78028Uzbekistan
      PHINHY-L78029Uzbekistan
      PHINHY-L78030Uzbekistan
      PHINHY-L78031Switzerland
      PHINHY-L78032Uzbekistan
      PHINHY-L78033Switzerland
      PHINHY-L78034Uzbekistan
      PHINHY-L78035Slovenia
      PHINHY-L78036Switzerland
      PHINHY-L78037Uzbekistan
      PHINHY-L78038Germany
      PHINHY-L78039Switzerland
      PHINHY-L78040Switzerland
      PHINHY-L78041Italy
      PHINHY-L78042United Arab Emirates
      PHINHY-L78043Switzerland
      PHINHY-L78044Slovenia
      PHINHY-L78045Uzbekistan
      PHINHY-L78046Switzerland
      PHINHY-L78047Switzerland
      PHINHY-L78048Uzbekistan
      PHINHY-L78049Switzerland
      PHINHY-L78050Switzerland
      PHINHY-L78051Slovenia
      PHINHY-L78052Uzbekistan
      PHINHY-L78053Switzerland
      PHINHY-L78054Switzerland
      PHINHY-L78055Switzerland
      PHINHY-L78056Ukraine
      PHINHY-L78057Ukraine
      PHINHY-L78058Slovenia
      PHINHY-L78059Ukraine
      PHINHY-L78060Ukraine
      PHINHY-L78061Switzerland
      PHINHY-L78062Ukraine
      PHINHY-L78063Slovenia
      PHINHY-L78064Uzbekistan
      PHINHY-L78065Switzerland
      PHINHY-L78066Slovenia
      PHINHY-L78067Japan
      PHINHY-L78068Slovenia
      PHINHY-L78069Uzbekistan
      PHINHY-L78070Switzerland
      PHINHY-L78071Uzbekistan
      PHINHY-L78072Japan
      PHINHY-L78073Uzbekistan
      PHINHY-L78074Japan
      PHINHY-L78075Switzerland
      PHINHY-L78076Switzerland
      PHINHY-L78077Egypt
      PHINHY-L78078Germany
      PHINHY-L78079Uzbekistan
      PHINHY-L78080Switzerland
      PHINHY-L78081Switzerland
      PHINHY-L78082Germany
      PHINHY-L78083Italy
      PHINHY-L78084Switzerland
      PHINHY-L78085Czech Republic
      PHINHY-L78086Belgium
      PHINHY-L78087Slovenia
      PHINHY-L78088Colombia
      PHINHY-L78089Switzerland
      PHINHY-L78090Turkey
      PHINHY-L78091Japan
      PHINHY-L78092France
      PHINHY-L78093Japan
      PHINHY-L78094Slovenia
      PHINHY-L78095Spain
      PHINHY-L78096Japan
      PHINHY-L78097Switzerland
      PHINHY-L78098Switzerland
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, AbhishekAejae 

        Thanks for rajendraongole1 reply. You can try the following measure to achieve your need.

        Counts =
VAR __countsCountry =
    CALCULATE (
        COUNT ( 'Table'[Country] ),
        ALLEXCEPT ( 'Table', 'Table'[Country] )
    )
RETURN
    __countsCountry

Flag = 
IF ( [Counts] > 3, 1 )

Rank = 
VAR __rank =
    RANKX ( FILTER ( ALLSELECTED ( 'Table' ), [Flag] = 1 ), [Counts],, ASC, DENSE )
RETURN
    __rank


        Best Regards,
        Yang
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know.         Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum