Filter rows in Power BI Matrix Based on Measure

[kellyylt]

I have a total of 3 tables - DIM_TARGET, FACT_PRODUCT, DIM_CALENDAR

DIM_TARGET (shown in below Excel), contains product information and the target as well as the start of the target's effective date.

FACT_PRODUCT contains the product's sales

DIM_DATE contains the calendar table and is joined one to many to FACT_PRODUCT.

What I need is to be able to filter the matrix's rows according to the products' effective date as shown below. 

How can I do this?

I am able to bring in the effective date using this DAX , but I am unable to filter the matrix table to show the relevant rows only.

EffectiveDate =

VAR _Maxslicer =

    MAX ( DIM_DATE[DATE] )

VAR _MAXWEEKEND =

    MAXX (

        FILTER (

            ALL ( DIM_TARGET ),

            DIM_TARGET[EFFECTIVE DATE] <= _Maxslicer

        ),

        DIM_TARGET[EFFECTIVE DATE]

    )

RETURN

    IF (

        MAX ( DIM_TARGET[EFFECTIVE DATE] ) = _MAXWEEKEND,

        _MAXWEEKEND,

        BLANK()

    )

[v-jtian-msft]

Hello,@rajendraongole1 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@kellyylt .I am glad to help you.
I'm glad to see that the code provided by @rajendraongole1  works for part of your requirement, I was looking at the current DAX formula that only shows data with a valid date equal to the filter date, whereas you want to show data with a valid date less than or equal to the filter date.
To accomplish this, the DAX formula can be modified to consider all rows less than or equal to the filter date when returning the value

EffectiveDate =
VAR _MaxSlicerDate = MAX(DIM_DATE[DATE])
VAR _MaxEffectiveDate =
    MAXX(
        FILTER(
            ALL(DIM_TARGET),
            DIM_TARGET[EFFECTIVE DATE] <= _MaxSlicerDate
        ),
        DIM_TARGET[EFFECTIVE DATE]
    )

RETURN
    IF (
        MAX ( DIM_TARGET[EFFECTIVE DATE] ) <= _MAXWEEKEND,//which I changed 
        _MAXWEEKEND,
        BLANK()
    )

(In fact I think from your screenshot description that you want to filter out startDate >= the date value selected by the slicer, not less than)
The example you provided filters 10% (2024/8/5) and 80% (2024/8/19) when the slicer selects 8/5
And when the second example: slicer selects 8/19, it only shows 80% (2024/8/19)
If my understanding is wrong, please do not hesitate to remind me.

You may need to disconnect the calendar table to connect the data table between to avoid the slicer's direct filtering (because you want to filter out values less than or equal to / or greater than or equal to the slicer's date, that's my guess, you can try, but please subject to your actual needs)
If possible, could you elaborate a bit more about your requirement, if you could share the test file (pbix file) again in the forum which doesn't contain sensitive data, it will help to find out the code issue, as the calculation environment is very important and it will affect the measure calculation result.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @kellyylt -Create below measure to filter products based on the effective date

EffectiveDate =
VAR _MaxSlicerDate = MAX(DIM_DATE[DATE])
VAR _MaxEffectiveDate =
MAXX(
FILTER(
ALL(DIM_TARGET),
DIM_TARGET[EFFECTIVE DATE] <= _MaxSlicerDate
),
DIM_TARGET[EFFECTIVE DATE]
)
RETURN
IF(
MAX(DIM_TARGET[EFFECTIVE DATE]) = _MaxEffectiveDate,
1, // Use a flag value to indicate that this row should be displayed
0 // Use a flag value to indicate that this row should not be displayed
)

Hope it works

[kellyylt]

Hello! This method seems to work for the second situation when I filter to 19/8/2024. However, for the first situation (filter from 5/8/2024), the matrix only shows row with 80% target instead of both 80% and 10%. I am thinking I need to sum and ignore the date column context filters but I can't seem to do it with SELECTEDVALUE()