How Do I Modify This Filter?

[Paul_Rockliffe]

I've got some DAX that looks like this:

   FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Calendar Table'[Date] ),
            "Known[X]", 'Calendar Table'[Date],
            "Known[Y]", [Forecast Measure]
        ),    
            AND (
                NOT ( ISBLANK ( Known[X] ) ),
                NOT ( ISBLANK ( Known[Y] ) )
                )
     )

 If I understand it correctly it is creating a Table that contains pairs of x and y coordinates based on amy Date and my Forecast Measure.  Filter is then removing any rows where any of the values are blank.  

This table feeds into some more DAX and that is being used in a line-chart.  On the Report Page I have a filter that restricts the page to a single Financial Year.  The DAX above is not being filtered by that filter, so my result shows the calculation across multiple years rather than just the current year.

Can anyone explain how I modify the above so the filter context of the page is applied or how I expand the filter definition so that it also removes rows where the Calendar Date is not in page's filter context?

The page filter is on a financial Year name, eg "2023-24", which then filters the Calendar Table Dates, so if the page context cannot be passed to this bit of my DAX, then the solution will need to be based on matching this text value rather than a date range.

I hope that makes sense and that it's relatively simple to solve?

Thanks

[OwenAuger]

Hi @Paul_Rockliffe 

In this particular case, you can replace

ALLSELECTED ( 'Calendar Table'[Date] )

with either this:

ALLSELECTED ( 'Calendar Table' )

or this:

CALCULATETABLE (
    VALUES ( 'Calendar Table'[Date] ),
    ALLSELECTED ( 'Calendar Table' )
)

The original expression returned all values of 'Calendar Table'[Date] in the shadow filter context of that specific column (roughly overall context of the visual), and in your example, there were no filters whatsoever on that specific column.

Either of the above expressions will take into account any filters on 'Calendar Table' (in the shadow filter context) and return the corresponding Date values (or related rows).

Regards