topic Re: DAX Filter Data based on Certain Date Range in DAX Commands and Tips

DAX Filter Data based on Certain Date Range

Anonymous — Fri, 06 Jan 2023 08:27:49 GMT

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

Re: DAX Filter Data based on Certain Date Range

tamerj1 — Fri, 06 Jan 2023 05:52:15 GMT

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 )

Re: DAX Filter Data based on Certain Date Range

Mahesh0016 — Fri, 06 Jan 2023 04:35:07 GMT

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

Re: DAX Filter Data based on Certain Date Range

Mahesh0016 — Fri, 06 Jan 2023 04:44:59 GMT

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.

Re: DAX Filter Data based on Certain Date Range

Anonymous — Fri, 06 Jan 2023 06:53:53 GMT

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

Re: DAX Filter Data based on Certain Date Range

Anonymous — Fri, 06 Jan 2023 08:27:08 GMT

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 .

Re: DAX Filter Data based on Certain Date Range

tamerj1 — Fri, 06 Jan 2023 08:05:43 GMT

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 )

Re: DAX Filter Data based on Certain Date Range

Anonymous — Fri, 06 Jan 2023 08:25:48 GMT

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

Re: DAX Filter Data based on Certain Date Range

tamerj1 — Fri, 06 Jan 2023 10:22:47 GMT

Anonymous

Please explain further

Re: DAX Filter Data based on Certain Date Range

Anonymous — Mon, 09 Jan 2023 00:34:33 GMT

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

Re: DAX Filter Data based on Certain Date Range

Anonymous — Mon, 16 Jan 2023 09:59:24 GMT

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.