Forum Discussion

BaSan's avatar
BaSan
New Member
1 year ago
Solved

Need Help: Dynamic Filtering in Power BI: Show All Relevant Items Beyond Initial Top 8

Hello everyone,

I am seeking a help. 

I am building a Power BI report on closed project details since 2000, with visuals organized by:

  1. Closed projects by Year
  2. Closed projects by Region
  3. Closed projects by Country (Top N filter applied)
  4. Closed projects by Sector (Top N filter applied)

I am using the Top N feature for the Country and Sector visuals to initially display only the top 8 items by project closed counts.

Challenge: Currently, if I click on a specific Country (e.g., Brazil), the Sector visual still only displays the sectors within the top 8 list, rather than dynamically updating to show all sectors in Brazil. I need the Sector visual to display all sectors associated with the selected country, not just those that were in the original Top 8.

Likewise, if I click on a sector, I want the Country visual to display all relevant countries involved in that sector, even if they were not in the initial Top 8 list.

Goal: 

  • When I select a country, the Sector visual dynamically shows all sectors associated with that country.
  • When I select a sector, the Country visual dynamically shows all countries associated with that sector.

Essentially, I want the cross-filtering to override the initial Top N filter, showing all relevant items associated with the selection, not just those in the Top 8.

If anyone has suggestions or a workaround to achieve this dynamic, context-sensitive filtering, I would really appreciate your help!

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi BaSan ,

     

    Here I create a sample to show you how to achieve your goal. I think you can try ISFILTERED() and IF() function.

    Dynamic TopN for Country = 
VAR _TopN =
    IF (
        MAX ( 'Table'[Country] )
            IN SUMMARIZE (
                TOPN (
                    8,
                    SUMMARIZE (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Country],
                        "Count", CALCULATE ( SUM ( 'Table'[Value] ) )
                    ),
                    [Count], DESC
                ),
                [Country]
            ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Country] ), 1, _TopN )
    Dynamic TopN for Sector = 
VAR _TopN =
    IF (
        MAX ( 'Table'[Sector] )
            IN SUMMARIZE (
                TOPN (
                    8,
                    SUMMARIZE (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Sector],
                        "Count", CALCULATE ( SUM ( 'Table'[Value] ) )
                    ),
                    [Count], DESC
                ),
                [Sector]
            ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Country] ), 1, _TopN )

    Add above measure into Country/Sector visual and set it to show items when value  = 1.

    Since I have less data in my sample, I show Top1. So when by default it will show Top1 in Country/Sector visual.

    When I select C3 in country, the Sector visual will show all sector in this coutry.

    When I select S2 in Sector, the Coutry visual will show all country in this sector.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi BaSan  It is better to provide dummy data and your expected result to accurate solution.

     

    The TOPN function is used to return the top N rows of a table based on a specified expression. It is more static and does not dynamically adjust based on user selections. When you apply a TOPN filter, it limits the data to the top N items based on the initial criteria, and this filter remains fixed unless manually changed.

    However, I would like to suggest first rank country and sector and then evaluate top country and sector. You could try this method:

     

    CountryRank = RANKX(ALL('Table'[Country]), CALCULATE(SUM('Table'[ClosedProjects])), , DESC, DENSE)
SectorRank = RANKX(ALL('Table'[Sector]), CALCULATE(SUM('Table'[ClosedProjects])), , DESC, DENSE)

     

     

    and Top:

     

    top8Country = IF(CountryRank <= 8, 1, 0)
top8Sector = IF(SectorRank<= 8, 1, 0)

     

     

    Place this measure in filter this visual section and set value to 1. This way, when you select a country or sector, the visuals update to show all relevant items, not just the top N.


    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

     

    Best Regards,
    Shahariar Hafiz

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi BaSan ,

     

    Here I create a sample to show you how to achieve your goal. I think you can try ISFILTERED() and IF() function.

    Dynamic TopN for Country = 
VAR _TopN =
    IF (
        MAX ( 'Table'[Country] )
            IN SUMMARIZE (
                TOPN (
                    8,
                    SUMMARIZE (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Country],
                        "Count", CALCULATE ( SUM ( 'Table'[Value] ) )
                    ),
                    [Count], DESC
                ),
                [Country]
            ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Country] ), 1, _TopN )
    Dynamic TopN for Sector = 
VAR _TopN =
    IF (
        MAX ( 'Table'[Sector] )
            IN SUMMARIZE (
                TOPN (
                    8,
                    SUMMARIZE (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Sector],
                        "Count", CALCULATE ( SUM ( 'Table'[Value] ) )
                    ),
                    [Count], DESC
                ),
                [Sector]
            ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Country] ), 1, _TopN )

    Add above measure into Country/Sector visual and set it to show items when value  = 1.

    Since I have less data in my sample, I show Top1. So when by default it will show Top1 in Country/Sector visual.

    When I select C3 in country, the Sector visual will show all sector in this coutry.

    When I select S2 in Sector, the Coutry visual will show all country in this sector.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.