Dynamically Select Table based on external filter selection

Hi jimmyhua ,

 

To achieve the dynamic filtering you’re aiming for, we need to ensure that the formula can distinguish between when a division is selected and when it is not, while recalculating the top 10 based on the selection. The current solution defaults to the organization-level top 10 due to the way UNION works; it ends up creating an overall set that doesn’t dynamically recalculate when filters change.

To correct this behavior, let’s refactor the code to use a conditional FILTER logic that only calculates top 10 based on the current filter context of divisions. Here’s an updated approach that uses conditional ranking logic without relying on UNION, which can lead to static results.

Top 10 Backlog Dynamic = 
VAR DivisionFilter = ISFILTERED(PJ401[Div Name])  // Check if a division is selected

// Step 1: Calculate backlog for each project
VAR DivisionMonthTable =
    ADDCOLUMNS(
        SUMMARIZE(
            PJ401,
            PJ401[Div Name],
            PJ401[Date],
            PJ401[Project ID],
            PJ401[Project Name]
        ),
        "Backlog",
            CALCULATE(
                SUMX(PJ401, [CM CV] * [CV%] - [Inception-to-date Revenue]),
                KEEPFILTERS(PJ401[Date]),
                KEEPFILTERS(PJ401[Project ID])
            )
    )

// Step 2: Apply conditional ranking
VAR RankedTable =
    ADDCOLUMNS(
        DivisionMonthTable,
        "RankByBL",
            IF(
                DivisionFilter,
                RANKX(
                    FILTER(
                        DivisionMonthTable,
                        [Div Name] = EARLIER([Div Name])
                        && MONTH([Date]) = MONTH(EARLIER([Date]))
                        && YEAR([Date]) = YEAR(EARLIER([Date]))
                    ),
                    [Backlog],
                    ,
                    DESC,
                    DENSE
                ),
                RANKX(
                    FILTER(
                        DivisionMonthTable,
                        MONTH([Date]) = MONTH(EARLIER([Date]))
                        && YEAR([Date]) = YEAR(EARLIER([Date]))
                    ),
                    [Backlog],
                    ,
                    DESC,
                    DENSE
                )
            )
    )

// Step 3: Filter for top 10 based on the calculated rank
RETURN
    FILTER(
        RankedTable,
        [RankByBL] <= 10
    )

This approach should yield the correct top 10 results, whether a division is selected or not, and automatically adapt to your filter selections.

 

Best regards,