Forum Discussion

jflores's avatar
jflores
Frequent Visitor
1 year ago
Solved

Reverse Filter Measure not working

Hello Community,

 

I'm an Inventory Items table that contains, amount other fields, Item ID, Description and Last Movement Date.  I'm trying to get the a date slicer to give me the items OUTSIDE the selected dates.  I have the following Measure.

 

Reverse Date Measure =
var SelectedMinDate=MIN(Date_Helper[Date])
var SelectedMaxDate=MAX(Date_Helper[Date])
VAR DebugCheck = IF(
    ISBLANK(SelectedMinDate) || ISBLANK(SelectedMaxDate),
    "No Slicer Selection",
    "Slicer Active"
)

Return
IF(
    DebugCheck = "No Slicer Selection",
   DebugCheck,
CALCULATE(
    COUNTROWS('Item Master') ,
 ('Item Master'[Last_Movement_Date] < MIN(Date_Helper[Date]) ||'Item Master'[Last_Movement_Date] > MAX(Date_Helper[Date]))
  && NOT(ISBLANK('Item Master'[Last_Movement_Date]))
)
)
 
My problem is my count rows doesn't work in reverse.  If I change my filter to ('Item Master'[Last_Movement_Date] > MIN(Date_Helper[Date]) ||'Item Master'[Last_Movement_Date] < MAX(Date_Helper[Date])) then it works.  However in Reverse it doesn't count any rows.
 
I have my Item Table Linked to my Date_Helper Table.  Any help is greatly appreciated.
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Reverse Date Measure =
    VAR SelectedMinDate = MIN(Date_Helper[Date])
    VAR SelectedMaxDate = MAX(Date_Helper[Date])

    VAR DebugCheck = IF(
    ISBLANK(SelectedMinDate) || ISBLANK(SelectedMaxDate),
    "No Slicer Selection",
    "Slicer Active"
    )

    RETURN
    IF(
    DebugCheck = "No Slicer Selection",
    DebugCheck,
    CALCULATE(
    COUNTROWS('Item Master'),
    ALL(Date_Helper), -- Remove date slicer context temporarily
    'Item Master'[Last_Movement_Date] < SelectedMinDate || 'Item Master'[Last_Movement_Date] > SelectedMaxDate,
    NOT(ISBLANK('Item Master'[Last_Movement_Date]))
    )
    )

    If the slicer is based on a Date_Helper table, ensure that it is properly linked to your Item Master table by the Last_Movement_Date field.

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Reverse Date Measure =
    VAR SelectedMinDate = MIN(Date_Helper[Date])
    VAR SelectedMaxDate = MAX(Date_Helper[Date])

    VAR DebugCheck = IF(
    ISBLANK(SelectedMinDate) || ISBLANK(SelectedMaxDate),
    "No Slicer Selection",
    "Slicer Active"
    )

    RETURN
    IF(
    DebugCheck = "No Slicer Selection",
    DebugCheck,
    CALCULATE(
    COUNTROWS('Item Master'),
    ALL(Date_Helper), -- Remove date slicer context temporarily
    'Item Master'[Last_Movement_Date] < SelectedMinDate || 'Item Master'[Last_Movement_Date] > SelectedMaxDate,
    NOT(ISBLANK('Item Master'[Last_Movement_Date]))
    )
    )

    If the slicer is based on a Date_Helper table, ensure that it is properly linked to your Item Master table by the Last_Movement_Date field.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jflores ,

     

    -- I have my Item Table Linked to my Date_Helper Table.

    My suspicion is that the problem lies here. 

     

    The filter from the slicer: 

    MIN(Date_Helper[Date]) <= Date_Helper[Date] && MAX(Date_Helper[Date]) >= Date_Helper[Date]

    The filter from the dax: 

    'Item Master'[Last_Movement_Date] < MIN(Date_Helper[Date]) ||'Item Master'[Last_Movement_Date] > MAX(Date_Helper[Date])

     

    These two filters have no intersection, and under their combined effect (AND), 'Item Master' will not return any records.

     

    Please try disconnecting the relationship between the Item Table and the Date_Helper Table.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum