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: Closed projects by Year Closed projects by Region Closed p...
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.

     

shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper 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