Help needed with DAX code for filtering equipment based on date range and status change

[Matias_Avila]

Hello. I need to create a new table called 'Filtered_Equipment' that contains the rows that meet the following conditions:

Filtered_Equipment = (Delivery Date <= Upper Date OR State Change Date >= Lower Date) AND NOT( (Delivery Date > Upper Date OR State Change Date < Lower Date) )

Condition A = Delivery Date <= Upper Date
Condition B = State Change Date >= Lower Date
Condition C = Delivery Date > Upper Date
Condition D = State Change Date < Lower Date

Five Cases

The goal is to include in the new table only the equipment (rows) that were active (green) during the time range established by the lower date and the upper date. 

I need to be able to define the Lower Date and Upper Date of the Date range with slider (Data Slicer). In this case I am feeding it with an auxiliary table called DateTable (which has the same date range as the Source Table in its Delivery Date column).

I require the DAX code to use the data from the Delivery Date and Status Change Date fields of the original table and the Lower Date and Upper Date data from the Filter to evaluate the conditions and see if it is included or not in the new Filtered_Equipment table .
Below I detail the five cases of rows and whether or not they should pass the filter and become part of the new table:
Case 1: This equipment should NOT move to the new table because this row (equioment) is outside the date range defined by Lower Date and Upper Date (It meets A but meets D, if it meets C or D it is left out)
Case 2: It must move to the new table because this row (equipment) meets the condition that the Status Change Date is greater than the Lower Date of the Filter (Meets A and does not meet C or D, if it does not meet C or D and meets A or B then if it goes to the new table).
Case 3: It must move to the new table because this row (equipment) meets A and B and does not meet C or D.
Case 4: It must move to the new table because this row (equipment) meets A and does not meet either C or D.
Case 5: It should NOT move to the new table because this row (equipment) is outside the date range defined by Lower Date and Upper Date (it meets B but it meets C and that is why it is left out)
Subcases:
If there is no data in Delivery Date: the comparison returns False, this must help, since C or D false have a NOT in front and the AND condition must return TRUE if the first memeber is TRUE.
If there is no data in Status Change Date: the comparison returns False, this works for me for the same reason stated above.

I understand that these conditions may seem contradictory, let me clarify that the second member of the AND, which has a NOT in front of it, is necessary to exclude cases 1 and 5. Let me explain: rows may satisfy conditions A or B, but if they meet one of conditions C or D, they should be excluded from the new table. So, if they don't satisfy C nor D, the second member of the AND becomes TRUE, and therefore the row is included.

Data flow

I am using DateTable as an auxiliary column to define the upper date and lower date

The code I have developed so far is as follows:

Filtered_Equipment =
FILTER(
ALL('BBII BAIR HUGGER')
,
(
(
'BBII BAIR HUGGER'[Delivery Date] <= MAX(TablaDeFechas[Date])
||
'BBII BAIR HUGGER'[State Change Date] >= MIN(TablaDeFechas[Date])
)

&&
(
NOT(
'BBII BAIR HUGGER'[Delivery Date] >= MAX(TablaDeFechas[Date])
||
'BBII BAIR HUGGER'[State Change Date] <= MIN(TablaDeFechas[Date])
)
)
)
)

It is not filtering the rows when I change the value of the lower Date and Upper Date with the slider, but instead it only let through two rows (which are the only ones that have a status change date). What I expected is that it would give me a greater number of rows in the new table since not only these two rows meet the conditions. Additionally, if I define the date range of the filter to one where these two devices are not active, they continue to appear.
I would appreciate any help.

[Anonymous]

Hi @Matias_Avila ,

I think the problem with your code is that you are using the MAX and MIN functions to get the upper and lower dates from the DateTable, which will return the same values regardless of the slicer selection. You should use the SELECTEDVALUE function instead, which will return the value that is currently selected by the slicer, or a default value if nothing is selected. 

You can modify the DAX code:

Filtered_Equipment =
FILTER (
    ALL ( 'BBII BAIR HUGGER' ),
    (
        (
            'BBII BAIR HUGGER'[Delivery Date]
                <= SELECTEDVALUE ( TablaDeFechas[Date], MAX ( TablaDeFechas[Date] ) )
                || 'BBII BAIR HUGGER'[State Change Date]
                    >= SELECTEDVALUE ( TablaDeFechas[Date], MIN ( TablaDeFechas[Date] ) )
        )
            && (
                NOT (
                    'BBII BAIR HUGGER'[Delivery Date]
                        >= SELECTEDVALUE ( TablaDeFechas[Date], MAX ( TablaDeFechas[Date] ) )
                        || 'BBII BAIR HUGGER'[State Change Date]
                            <= SELECTEDVALUE ( TablaDeFechas[Date], MIN ( TablaDeFechas[Date] ) )
                )
            )
    )
)

This code will use the selected value from the slicer as the upper and lower dates, or the maximum and minimum values from the DateTable if nothing is selected. This way, you can filter the table by any date range you want.

Best Regards

Yilong Zhou

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

[Matias_Avila]

Hi Yilong Zhou,

Firstly, I want to express my gratitude for your assistance regarding the DAX code for filtering equipment based on date range and status change. Unfortunately, I'm still encountering some issues with the implementation.

Despite incorporating your suggested modifications to the code, the filtering outcome remains inconsistent. Specifically, the filtered table still retains only two equipment entries  (the only ones that have a status change date), and adjusting the lower and upper date values through the slicer does not affect the result. It appears that something within the code, particularly in the comparison logic, is not functioning as expected.

I've thoroughly reviewed the modifications made to the DAX code and ensured that the SELECTEDVALUE function is appropriately utilized to capture the slicer selections for date ranges. However, the filtering behavior remains unchanged.

Given the complexity of the conditions and logic involved, I suspect there might be a subtle error or oversight within the code that I'm unable to identify. Could you kindly review the DAX code once again and provide further insights or potential adjustments that might resolve the issue? Your expertise and guidance in this matter would be immensely appreciated. Thank you for your continued support.

Best regards,

Matias

[Anonymous]

Hi @Matias_Avila ,

In response to your question, I think the first thing you can do is to make sure that the Calendar table contains the relevant date columns, that the Table table contains the Start of Range and End of Range columns, and that the slicer is properly connected to the date columns in the Calendar table.

The goal is to create a measure that checks whether the “Range start” and “Range end” fall within the date range selected by the slicer. If they do, the output should be 0; otherwise, it should be 1. You can try this Dax codes.

MEASURE =
VAR _minDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _maxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _rangeStart =
    CALCULATE (
        MAX ( 'Table'[Range start] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Employeeid] = MAX ( 'Table'[Employeeid] ) )
    )
VAR _rangeEnd =
    CALCULATE (
        MAX ( 'Table'[Range end] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Employeeid] = MAX ( 'Table'[Employeeid] ) )
    )
VAR _slicer =
    GENERATESERIES ( _minDate, _maxDate )
VAR _t =
    GENERATESERIES (
        IF ( _rangeStart <> BLANK (), _rangeStart, 0 ),
        IF ( _rangeEnd <> BLANK (), _rangeEnd, 0 )
    )
VAR _count =
    COUNTROWS ( _slicer )
VAR _except =
    COUNTROWS ( EXCEPT ( _slicer, _t ) )
RETURN
    IF ( _count <> _except, 0, 1 )

Best Regards

Yilong Zhou

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