Filter top N elements on both levels of hierarchy

[bartek_bartek]

Hello everyone,

I'm having trouble creating a bar chart that displays the top 10 states and top 10 cities in the same visual.

I'm using the Superstore dataset from Kaggle (available here) the following data model:

The chart I'm trying to make looks like this:

• X-axis: Hierarchy – State → City

• Y-axis: Total Sales

The chart I’m trying to build should allow me to show both levels, and apply a filter to only display the top 10 items based on sales rank.

However, the Sales Rank filter is not working when both State and City are present in the hierarchy. It does work:

• When I drill down to a specific state and see its cities

• When I go directly to the City level

• When I remove City from the hierarchy

But I would like to retain both State and City in the same chart and still apply the filtering correctly.

Here are my measures:

Total Sales by Level = 
SWITCH(
    TRUE(),
    ISINSCOPE(DimTerritory[City]) && ISINSCOPE(DimTerritory[State]),
        [Total Sales by City],
    ISINSCOPE(DimTerritory[City]) && NOT ISINSCOPE(DimTerritory[State]),
        [Total Sales by City],
    ISINSCOPE(DimTerritory[State]) && NOT ISINSCOPE(DimTerritory[City]),
        [Total Sales by State],
    BLANK()
)

Total Sales by State = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(DimTerritory, DimTerritory[State])
)

Total Sales by City = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(DimTerritory, DimTerritory[City])
)

Sales Rank = 
VAR isStateLevel = ISINSCOPE(DimTerritory[State]) && NOT ISINSCOPE(DimTerritory[City])
VAR isCityLevel = ISINSCOPE(DimTerritory[City]) && NOT ISINSCOPE(DimTerritory[State])
VAR isDrillDown = ISINSCOPE(DimTerritory[City]) && ISINSCOPE(DimTerritory[State])

RETURN
SWITCH(
    TRUE(),
    isStateLevel && [Rank by State] <= 10, 1,
    isCityLevel && [Rank by City] <= 10, 1,
    isDrillDown && [Rank by State and City] <= 10, 1,
    0
)

Rank by State = 
IF(
    ISINSCOPE(DimTerritory[State]) && NOT ISINSCOPE(DimTerritory[City]),
        RANKX(
            ALL(DimTerritory[State]),
            [Total Sales by State],
            ,
            DESC,
            Dense
        )
)

Rank by City = 
IF(
    ISINSCOPE(DimTerritory[City]) && NOT ISINSCOPE(DimTerritory[State]),
        RANKX(
            ALL(DimTerritory[City]),
            [Total Sales by City],
            ,
            DESC,
            Dense
        )
)

Rank by State and City = 
IF(
    ISINSCOPE(DimTerritory[City]) && ISINSCOPE(DimTerritory[State]),
        RANKX(
            FILTER(
                ALL(DimTerritory[State], DimTerritory[City]),
                DimTerritory[State] = MAX(DimTerritory[State])
            ),
            [Total Sales by City],
            ,
            DESC,
            DENSE
        )
)

What am I doing wrong here? Any help would be greatly appreciated!

Thanks in advance!

[jaineshp · Accepted solution]

Hey @bartek_bartek,

Based on your requirement to filter top N elements on both levels of hierarchy, here's a comprehensive solution that addresses the core issues in your current approach:

Problem Analysis

1. Hierarchy Context Issue: Your current measures don't properly handle the mixed hierarchy context where both State and City are visible
2. Ranking Logic Flaw: The ranking measures aren't correctly identifying which level should be ranked
3. Filter Application: The Sales Rank filter isn't working because it doesn't properly distinguish between hierarchy levels

Recommended Solutions

Step 1: Revised Core Measures

Total Sales = SUM(FactSales[Sales])

Total Sales by State =
CALCULATE(
[Total Sales],
REMOVEFILTERS(DimTerritory[City])
)

Total Sales by City =
CALCULATE(
[Total Sales],
REMOVEFILTERS(DimTerritory[State])
)

Step 2: Enhanced Hierarchy Detection

Current Level =
VAR StateInScope = ISINSCOPE(DimTerritory[State])
VAR CityInScope = ISINSCOPE(DimTerritory[City])
VAR StateCount = DISTINCTCOUNT(DimTerritory[State])
VAR CityCount = DISTINCTCOUNT(DimTerritory[City])

RETURN
SWITCH(
TRUE(),
StateInScope && CityInScope && StateCount = 1, "City",
StateInScope && CityInScope && StateCount > 1, "State",
StateInScope && NOT CityInScope, "State",
NOT StateInScope && CityInScope, "City",
"Unknown"
)

Step 3: Improved Ranking Measures

State Rank =
VAR CurrentLevel = [Current Level]
RETURN
IF(
CurrentLevel = "State",
RANKX(
ALL(DimTerritory[State]),
[Total Sales by State],
,
DESC,
DENSE
)
)

City Rank =
VAR CurrentLevel = [Current Level]
VAR CurrentState = MAX(DimTerritory[State])
RETURN
SWITCH(
CurrentLevel,
"City", RANKX(
FILTER(
ALL(DimTerritory[City], DimTerritory[State]),
DimTerritory[State] = CurrentState
),
[Total Sales],
,
DESC,
DENSE
),
BLANK()
)


Step 4: Unified Sales Rank Filter

Sales Rank Filter =
VAR CurrentLevel = [Current Level]
VAR StateRank = [State Rank]
VAR CityRank = [City Rank]
VAR TopN = 10

RETURN
SWITCH(
CurrentLevel,
"State", IF(StateRank <= TopN, 1, 0),
"City", IF(CityRank <= TopN, 1, 0),
0
)

Step 5: Display Measure

Sales Display Value =
VAR CurrentLevel = [Current Level]
VAR FilterValue = [Sales Rank Filter]

RETURN
IF(
FilterValue = 1,
SWITCH(
CurrentLevel,
"State", [Total Sales by State],
"City", [Total Sales],
BLANK()
),
BLANK()
)

Implementation Steps

Visual Configuration:

1. Add to Axis: Create hierarchy with State → City
2. Add to Values: Use Sales Display Value measure
3. Add to Filters: Use Sales Rank Filter measure and filter for value = 1

Alternative Approach (Simpler):

Top 10 Filter =
VAR IsExpanded = ISINSCOPE(DimTerritory[City]) && DISTINCTCOUNT(DimTerritory[State]) > 1
VAR IsStateDrillDown = ISINSCOPE(DimTerritory[City]) && DISTINCTCOUNT(DimTerritory[State]) = 1

RETURN
SWITCH(
TRUE(),
IsExpanded,
IF(
RANKX(ALL(DimTerritory[State]), [Total Sales by State],, DESC) <= 10,
1,
0
),
IsStateDrillDown,
IF(
RANKX(
FILTER(ALL(DimTerritory[City]),
DimTerritory[State] = MAX(DimTerritory[State])),
[Total Sales],, DESC
) <= 10,
1,
0
),
0
)

Key Benefits of This Solution

1. Proper Context Handling: Correctly identifies hierarchy levels
2. Dynamic Filtering: Works at both State and City levels
3. Performance Optimized: Uses efficient DAX patterns
4. Maintainable: Clear logic flow and easy to modif

This approach should resolve your filtering issues while maintaining the hierarchical structure you need.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,

Jainesh Poojara | Power BI Developer

[v-echaithra]

Hi @bartek_bartek ,

Thank you @ThxAlot , @jaineshp  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[jaineshp]

Hey @v-echaithra,

Thank you for the kind recognition - always happy to contribute to our community's success!

Best Regards,
Jainesh Poojara | Power BI Developer

[Ashish_Mathur]

Hi,

Please share a simple sample dataset and show the expected result in a Table format.

[ThxAlot · Accepted solution]

1. Such an outdated dataset from Tableau doesn't help you grasp delicacy of PBI;
2. Due to inherent mechanism of visual Filters, multi-level filter can't be achieved by a measure in Filters.

Apply a measure directly in the viz. 

[jaineshp · Accepted solution]

Hey @bartek_bartek,

Based on your requirement to filter top N elements on both levels of hierarchy, here's a comprehensive solution that addresses the core issues in your current approach:

Problem Analysis

1. Hierarchy Context Issue: Your current measures don't properly handle the mixed hierarchy context where both State and City are visible
2. Ranking Logic Flaw: The ranking measures aren't correctly identifying which level should be ranked
3. Filter Application: The Sales Rank filter isn't working because it doesn't properly distinguish between hierarchy levels

Recommended Solutions

Step 1: Revised Core Measures

Total Sales = SUM(FactSales[Sales])

Total Sales by State =
CALCULATE(
[Total Sales],
REMOVEFILTERS(DimTerritory[City])
)

Total Sales by City =
CALCULATE(
[Total Sales],
REMOVEFILTERS(DimTerritory[State])
)

Step 2: Enhanced Hierarchy Detection

Current Level =
VAR StateInScope = ISINSCOPE(DimTerritory[State])
VAR CityInScope = ISINSCOPE(DimTerritory[City])
VAR StateCount = DISTINCTCOUNT(DimTerritory[State])
VAR CityCount = DISTINCTCOUNT(DimTerritory[City])

RETURN
SWITCH(
TRUE(),
StateInScope && CityInScope && StateCount = 1, "City",
StateInScope && CityInScope && StateCount > 1, "State",
StateInScope && NOT CityInScope, "State",
NOT StateInScope && CityInScope, "City",
"Unknown"
)

Step 3: Improved Ranking Measures

State Rank =
VAR CurrentLevel = [Current Level]
RETURN
IF(
CurrentLevel = "State",
RANKX(
ALL(DimTerritory[State]),
[Total Sales by State],
,
DESC,
DENSE
)
)

City Rank =
VAR CurrentLevel = [Current Level]
VAR CurrentState = MAX(DimTerritory[State])
RETURN
SWITCH(
CurrentLevel,
"City", RANKX(
FILTER(
ALL(DimTerritory[City], DimTerritory[State]),
DimTerritory[State] = CurrentState
),
[Total Sales],
,
DESC,
DENSE
),
BLANK()
)


Step 4: Unified Sales Rank Filter

Sales Rank Filter =
VAR CurrentLevel = [Current Level]
VAR StateRank = [State Rank]
VAR CityRank = [City Rank]
VAR TopN = 10

RETURN
SWITCH(
CurrentLevel,
"State", IF(StateRank <= TopN, 1, 0),
"City", IF(CityRank <= TopN, 1, 0),
0
)

Step 5: Display Measure

Sales Display Value =
VAR CurrentLevel = [Current Level]
VAR FilterValue = [Sales Rank Filter]

RETURN
IF(
FilterValue = 1,
SWITCH(
CurrentLevel,
"State", [Total Sales by State],
"City", [Total Sales],
BLANK()
),
BLANK()
)

Implementation Steps

Visual Configuration:

1. Add to Axis: Create hierarchy with State → City
2. Add to Values: Use Sales Display Value measure
3. Add to Filters: Use Sales Rank Filter measure and filter for value = 1

Alternative Approach (Simpler):

Top 10 Filter =
VAR IsExpanded = ISINSCOPE(DimTerritory[City]) && DISTINCTCOUNT(DimTerritory[State]) > 1
VAR IsStateDrillDown = ISINSCOPE(DimTerritory[City]) && DISTINCTCOUNT(DimTerritory[State]) = 1

RETURN
SWITCH(
TRUE(),
IsExpanded,
IF(
RANKX(ALL(DimTerritory[State]), [Total Sales by State],, DESC) <= 10,
1,
0
),
IsStateDrillDown,
IF(
RANKX(
FILTER(ALL(DimTerritory[City]),
DimTerritory[State] = MAX(DimTerritory[State])),
[Total Sales],, DESC
) <= 10,
1,
0
),
0
)

Key Benefits of This Solution

1. Proper Context Handling: Correctly identifies hierarchy levels
2. Dynamic Filtering: Works at both State and City levels
3. Performance Optimized: Uses efficient DAX patterns
4. Maintainable: Clear logic flow and easy to modif

This approach should resolve your filtering issues while maintaining the hierarchical structure you need.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,

Jainesh Poojara | Power BI Developer