Filter based on Measures

Hi Jyaul1122 ,

The problem you're encountering is due to how Power BI's filter context works. When you click the "Deleted" segment for a date, it filters the page for that specific date. However, the deleted task no longer exists in your data on that date, which results in a blank table. To solve this, you need to change the source of your chart and then use a special measure to control how the table is filtered, ensuring deleted items are shown from their last known date.

First, you need to create a new calculated table that will explicitly list every task that was added or deleted on each date. You can do this by navigating to the Modeling tab, clicking New Table, and entering the following DAX expression. This table will become the new source for your chart.

Changes Summary = 
VAR AllDates = VALUES('TASK'[Data Date])

VAR DatesWithPrevious =
    ADDCOLUMNS (
        AllDates,
        "PreviousDate", MAXX(FILTER(AllDates, [Data Date] < EARLIER([Data Date])), [Data Date])
    )

RETURN
GENERATE (
    DatesWithPrevious,
    VAR CurrentDate = [Data Date]
    VAR PreviousDate = [PreviousDate]

    VAR CurrentTasks = CALCULATETABLE(VALUES('TASK'[task code]), 'TASK'[Data Date] = CurrentDate)
    VAR PreviousTasks =
        IF(
            ISBLANK(PreviousDate),
            DATATABLE("task code", STRING, {}), -- Empty table for the very first date
            CALCULATETABLE(VALUES('TASK'[task code]), 'TASK'[Data Date] = PreviousDate)
        )

    VAR AddedTasks = EXCEPT(CurrentTasks, PreviousTasks)
    VAR DeletedTasks = EXCEPT(PreviousTasks, CurrentTasks)

    VAR AddedTable = ADDCOLUMNS(AddedTasks, "Status", "Added")
    VAR DeletedTable = ADDCOLUMNS(DeletedTasks, "Status", "Deleted")

    RETURN UNION(AddedTable, DeletedTable)
)

Next, you'll reconfigure your stacked column chart to use this new Changes Summary table. This change is crucial because it allows Power BI to understand whether your click corresponds to an "Added" or "Deleted" status. Set the X-axis to Changes Summary[Data Date] and the Legend to Changes Summary[Status]. For the Y-axis, you'll need a new measure that correctly handles the positive and negative values for your chart.

Value for Chart = 
IF(
    SELECTEDVALUE('Changes Summary'[Status]) = "Deleted",
    -COUNT('Changes Summary'[task code]),
    COUNT('Changes Summary'[task code])
)

With the chart updated, you can create the final measure that will be applied as a filter to your table visual. This measure will check your selection in the chart and intelligently decide which rows from your original table should be visible. It will return a 1 for rows that should be shown and a 0 for those that should be hidden.

Best regards,