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.

     

Mahesh0016's avatar
Mahesh0016
Super User
3 years ago

Hello Anonymous please can you share Data modeling of calendar and QCDailyTarget tables.


  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi , Unable to share since have a lot of tables linked  (Unable to see clearly) .Both of the start date and end date linked on date column

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Anonymous 
      If you want to keep the relationship perhaps for other visuals and measures, you can modify my previour filter measure as follows.

      FilterMeasure =
IF (
    NOT ISEMPTY (
        FILTER (
            ALL ( QCDailyTarget ),
            QCDailyTarget[Start Date] >= MIN ( 'Calendar'[Date] )
                && QCDailyTarget[End Date] <= MAX ( 'Calendar'[Date] )
        )
    ),
    1
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi, your solution only show 1 row data .. Sorry for bad explaination at the question but i want actually are all type and calculated filter are shown except for Jr QC which will show 1 data which are filtered at respective date