Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Filter Data based on Certain Date Range

Hi all , need some help to filter the data based on certain range Below are the sample data that I have. Would like to filter the data based on start date and end date to get target output based o...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to set the two relationships between Calendar table and QCDailyTarget table to inactive.

    Or your visual will be impacted by the relationship.

    I suggest you to create a measure to filter your visual.

    FilterMeasure = 
VAR _RANGESTART =
    MIN ( 'Calendar'[Date] )
VAR _RANGEEND =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        SELECTEDVALUE ( QCDailyTarget[Start Date] ) <= _RANGESTART
            && SELECTEDVALUE ( QCDailyTarget[End Date] ) >= _RANGEEND,
        1,
        0
    )

    Add this measure into visual level filter and set it to show items when value = 1.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 

Please place the following measure in the filter pane of the visual or the report page, select "is not blank" then apply the filter.

FilterMeasure =
IF (
    NOT ISEMPTY (
        FILTER (
            QCDailyTarget,
            QCDailyTarget[Start Date] >= MIN ( 'Calendar'[Date] )
                && QCDailyTarget[End Date] <= MAX ( 'Calendar'[Date] )
        )
    ),
    1
)