Forum Discussion

cjgreene's avatar
cjgreene
Regular Visitor
2 years ago
Solved

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

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

case_id  assessment_ended
11/1/23
11/4/23
11/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
11/1/230
11/4/230
11/6/231

 

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

case_id ended_at Indicator
11/1/230
11/4/231
11/6/230

 

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.