Dynamically Select Table based on external filter selection

[jimmyhua]

here is my code,  let me explain what do I intend it to do.  I want to select top 10 by division by month when a division is selected from outside filter. when there is no division selected, I want to see Top 10 by the entire organization.  however, the result I got is defaulted to no division selection and top 10 for entire organization, when i then make a selection of divisions, it only filter down from the top 10 for entire org rather than give me a top 10 for the division.  how do i fix the logic?  I tried to use if statement, but DAX will not allow if to filter tables.  hence you see the converluted soluation i came up with.  Hope someone here can provie me a solution.  Thanks in advance.

Top 10 Backlog Dynamic =

VAR DivisionFilter = ISFILTERED(PJ401[Div Name])  // Check if Div Name is filtered

// 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(VALUES(PJ401[Div Name])),

                KEEPFILTERS(PJ401[Date]),

                KEEPFILTERS(VALUES(PJ401[Project ID]))

            )

    )

// Step 2a: Rank for division-level filtering

VAR DivisionRankedTable =

    ADDCOLUMNS(

        DivisionMonthTable,

        "RankByBL",

            RANKX(

                FILTER(

                    DivisionMonthTable,

                    [Div Name] = EARLIER([Div Name])

                    && MONTH([Date]) = MONTH(EARLIER([Date]))

                    && YEAR([Date]) = YEAR(EARLIER([Date]))

                ),

                [Backlog],

                ,

                DESC,

                DENSE

            ),

        "IsDivisionFilter", TRUE()  // Mark rows for division filter case

    )

// Step 2b: Rank for organization-wide filtering (top 10 across all divisions)

VAR OverallRankedTable =

    ADDCOLUMNS(

        DivisionMonthTable,

        "RankByBL",

            RANKX(

                FILTER(

                    DivisionMonthTable,

                    MONTH([Date]) = MONTH(EARLIER([Date]))

                    && YEAR([Date]) = YEAR(EARLIER([Date]))

                ),

                [Backlog],

                ,

                DESC,

                DENSE

            ),

        "IsDivisionFilter", FALSE()  // Mark rows for overall ranking case

    )

// Step 3: Combine both tables using UNION

VAR CombinedTable = UNION(DivisionRankedTable, OverallRankedTable)

// Step 4: Filter combined table based on DivisionFilter and select top 10 accordingly

RETURN

    FILTER(

        CombinedTable,

        (DivisionFilter && [IsDivisionFilter] = TRUE() && [RankByBL] <= 10)

        || (NOT DivisionFilter && [IsDivisionFilter] = FALSE() && [RankByBL] <= 10)

    )

[DataNinja777]

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,

[jimmyhua]

@DataNinja777 
I took out the 

KEEPFILTERS(PJ401[Project ID]

and it fixed the Cannot convert Type Text to type True/False error, then I ran again, it still only returns organization-level top10.  when I then select a division, it still filter on the organization-level table.  wonder the IF statement is actually working.

[jimmyhua]

initially, I tried the structure you suggested, but the IF statement keeps giving me issue with Cannot convert value '10142.A031' of type Text to type True/False, 10142A031 is one of the project ID.  so I broke out the table into two parts (divisional and without division selection).  Then I tried to use IF statement to select one, it still does not work because IF cannot be used on a table.