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 on calendar (which i use slicer in the dashboard).  I also have a calendar table with date column to filter all of the data in the report


This is what i tried but it still did not give what I wanted : 

Calculated Column Filter = CALCULATE (
    VALUES ( QCDailyTarget[Target Output] ),
    FILTER (
        'Calendar',
        'Calendar'[Date] <= EARLIER (QCDailyTarget[Start Date] ) && 'Calendar'[Date] >= EARLIER (QCDailyTarget[End Date] )
    )
)
 

My expected output is will only show all data and 1 data for Jr QC which is in the range of start date and end date. Please help give me a better approach to this problem

Thanks and Regards,

Hairul


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

     

10 Replies

  • tamerj1's avatar
    tamerj1
    Community 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
)
  • 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
)
  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    Anonymous 

     

     

    Calculated Column Filter =
    CALCULATE (
        VALUES ( Filter_Data_Table[Target Output] ),
        FILTER (
            'Calendar_Table',
            'Calendar_Table'[Date] <= EARLIER (Filter_Data_Table[Start Date] ) && 'Calendar_Table'[Date] >= CALCULATE(MIN(Filter_Data_Table[End Date]),USERELATIONSHIP(Filter_Data_Table[End Date],Calendar_Table[Date])) )
        )
    **If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Mahesh0016 , this produces same output as what i have tried and doesnt filter it out based on the dates . May i ask if you put the relationship only on end date to the calendar? Also , sorry for the bad explaination at the question but what i want is all the data shown based on date filter .