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.

     

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 

 

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

Anonymous 

Please explain further 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tamerj1 , 

    So referring to my initial question , the expected output are Jr QC with the value of 3 and with other types  after using the month and year slicer December 2022.   The result i am getting right now is getting all 3 rows of Jr QC and other types ( which intended only 1 Jr QC).

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.