Forum Discussion

Matias_Avila's avatar
Matias_Avila
Frequent Visitor
2 years ago

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

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 >=...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Matias_Avila
Frequent Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.