Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jimmyhua
Helper I
Helper I

Dynamically Select Table based on external filter selection

 
4 REPLIES 4
jimmyhua
Helper I
Helper I

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)
    )

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,

@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.

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors