Filter based on Measures

[Jyaul1122]

Hello, I have task table , there are multiple task code are coming based on data date and for that project.

Data Date	Project	task code	Finish Date
31-Aug-25	Music	Music1	12-Sep-27
31-Aug-25	Music	Music2	4-Oct-27
31-Aug-25	Music	Music3	26-Oct-27
31-Aug-25	Movie	Movie1	17-Nov-27
4-Sep-25	Music	Music2	9-Dec-27
4-Sep-25	Music	Music3	31-Dec-27
4-Sep-25	Movie	Movie1	22-Jan-28
11-Sep-25	Festival	Festival1	13-Feb-28
11-Sep-25	Festival	Festival3	28-Mar-28
11-Sep-25	Music	Music4	19-Apr-28

and I need to find number of task code which have new or deleted by data date (compare with previous data date). so that I wrote two measures like:

Added =

var prev_=CALCULATETABLE(

            VALUES('TASK'[task code]),

            FILTER(ALL('TASK'[Data Date]),'TASK'[Data Date]<MAX('TASK'[Data Date]))

            )

var this_=VALUES('TASK'[task code])

RETURN COUNTROWS(EXCEPT(this_,prev_))

and 

Deleted=

var prev_=

CALCULATETABLE(

            VALUES('TASK'[task code]),

            FILTER(ALL('TASK'[Data Date]),'TASK'[Data Date]<MAX('TASK'[Data Date]))

            )  

var this_=VALUES('TASK'[task code])

RETURN  COUNTROWS(EXCEPT(prev_,this_))*-1

 and display on column stack chart

and I have table chart: 

What  I need whenever I select any number from column chart then table chart needs to be filter.

For exaple if i press 4, 31-Aug, I need to show only four added task row,

I i press -1, 4-Sep then I need to show only one deleted task.

For Added, Its working fine but for Deleted not working fine.

How can I achieve this ??

[DataNinja777 · Accepted solution]

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,

[techies]

Hi @Jyaul1122 you can also achieve this using power query m code, do you want to try?

[ValtteriN]

Hi,

As mentioned in the other replies you could do this in M and the reason this is happening is filter contex. Basically you need to be able to show only either added or deleted rows if you want to have this interaction while clicking the graph. 

One solution is to have a combined measure:

Filter = SWITCH(TRUE(),

NOT(HASONEFILTER(TestFor[Test])),

[Added]+[Deleted],

    MAX(TestFor[Test])="Added",

[Added],

MAX(TestFor[Test])="Deleted",

[Deleted])

Then use this in the visual:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/