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 gr...
  • Anonymous's avatar
    Anonymous
    2 years 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

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi AbhishekAejae - Can you please share some sample data in excel copy, it helps to analyse further.

 

AbhishekAejae's avatar
AbhishekAejae
Regular Visitor
2 years 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
    2 years 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

    • AbhishekAejae's avatar
      AbhishekAejae
      Regular Visitor
      1 year ago

      Hi Anonymous  this is working but the slicers are not affecting the data. could you please help me with it? I have a device installed date hierarchy as a slicer and this date is not affecting it even though this date is in the same table