topic Re: Create a dynamic column/measure/parameter based on date slicer in DAX Commands and Tips

Create a dynamic column/measure/parameter based on date slicer

cjgreene — Thu, 04 Jan 2024 22:27:48 GMT

I have a dataset that stores information about cases and when they completed assessments:

case_id	assessment_ended
1	1/1/23
1	1/4/23
1	1/6/23

Using a column, measure, or parameter, I need to create an "Indicator" that changes based on my date slicer. For example, If my date slicer is set to 1/1/2023 - 1/7/2023, I would want to see:

case_id	ended_at	Indicator
1	1/1/23	0
1	1/4/23	0
1	1/6/23	1

If I change my date slicer to 1/1/2023 - 1/5/2023, I would want to see:

case_id	ended_at	Indicator
1	1/1/23	0
1	1/4/23	1
1	1/6/23	0

I have seen some other posts that say calculated columns can not be dynamic. I am wondering anyone is aware of any alternative solutions. Any tips/suggestions are welcome

Re: Create a dynamic column/measure/parameter based on date slicer

AlexisOlson — Thu, 04 Jan 2024 23:08:34 GMT

You can create an indicator measure that would work in a matrix or table visual, but you cannot have the actual data table in your model be responsive to slicers or filters (since they are not recalculated dynamically in response to report settings, only for data refreshes or other model updates).

Such a measure might look something like this:

Indicator = VAR _Ended = SELECTEDVALUE ( Table1[assesment_ended] ) VAR _SelectedDates = VALUES ( DateSlicer[Date] ) RETURN IF ( _Ended IN _SelectedDates, 1, 0 )

Re: Create a dynamic column/measure/parameter based on date slicer

Anonymous — Fri, 12 Jan 2024 09:56:30 GMT

Hi cjgreene

My Date table sample:

You can create a measure as follows.

Indicator = IF ( SELECTEDVALUE ( 'Table'[assessment_ended] ) > MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[assessment_ended] ) = MAX ( 'Date'[Date] ) - 1, 1, 0 )

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.