Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter rows in Power BI Matrix Based on Measure

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...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Anonymous -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

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi Anonymous -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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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