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 >=...
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.