topic Re: A measure that can be filtered by 2 date columns using 1 slicer in DAX Commands and Tips

A measure that can be filtered by 2 date columns using 1 slicer

Orman01 — Mon, 07 Jul 2025 22:51:18 GMT

Hi Everyone! I have this requirement to create a slicer where a measure should be filtered by both start date and end date. (ex. We need to see the total tickets created and completed from Jan 1 to Jan 31 (Between Date Slicer)). Tried using USERELATIONSHIP but it can only filter one date column.

CREATED_COMPLETED = CALCULATE(DISTINCTCOUNT('RAW'[TICKETNUMBER]),USERELATIONSHIP('RAW'[DATE CREATED],'Date Table'[Date]),USERELATIONSHIP('RAW'[DATE COMPLETED],'Date Table'[Date]))

Thank you.

Re: A measure that can be filtered by 2 date columns using 1 slicer

MasonMA — Mon, 07 Jul 2025 23:29:50 GMT

Orman01

Hi, in this case you would need to disconnect your Date table from RAW table and create one measure for filtering your report.

You can refer to my testFile. I used this demo measure below and you can adjust it based on your real situation.

Tickets_CreatedAndCompleted = CALCULATE( DISTINCTCOUNT('Tickets'[TICKETNUMBER]), FILTER( 'Tickets', 'Tickets'[DATE CREATED] >= MIN('Date Table'[Date]) && 'Tickets'[DATE CREATED] <= MAX('Date Table'[Date]) && 'Tickets'[DATE COMPLETED] >= MIN('Date Table'[Date]) && 'Tickets'[DATE COMPLETED] <= MAX('Date Table'[Date]) ) )

Hope it helps:)

Re: A measure that can be filtered by 2 date columns using 1 slicer

Jihwan_Kim — Tue, 08 Jul 2025 02:54:25 GMT

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

created and completed: = VAR _t = FILTER ( RAW, RAW[DATE COMPLETED] >= MIN ( 'Date Table'[Date] ) && RAW[DATE CREATED] <= MAX ( 'Date Table'[Date] ) ) RETURN COUNTROWS ( _t )

Re: A measure that can be filtered by 2 date columns using 1 slicer

Orman01 — Tue, 08 Jul 2025 05:14:39 GMT

Thank you. I appreciate your response, however, I'm not sure why but some filters are not showing accurate numbers.

Re: A measure that can be filtered by 2 date columns using 1 slicer

Orman01 — Tue, 08 Jul 2025 05:15:22 GMT

This works! Thanks a lot!