Dynamic Age Calculation Based on User Selected Cutoff Date

Hi Anonymous

Unfortunately as Yuliana mentioned, calculated columns don't respond to any filters within the report. Calculated columns are populated at report refresh, in an 'unfiltered' filter context.

For what you're trying to do, you would need to use something like a Dynamic Segmentation pattern (see DAX Patterns page).

1. Create an 'Age' table containing all possible Ages you might want to filter on.
2. Include the Age[Age] column in your visual.
3. Create a Member Count By Age measure following a Dynamic Segmentation type pattern:
   

   Member Count By Age =
   IF (
       ISFILTERED ( Age[Age] ),
       VAR SelectedCutoff =
           MAX ( Cutoff[cutoff_date] )
       RETURN
           CALCULATE (
               COUNTROWS ( 'Member' ),
               FILTER (
                   VALUES ( 'Member'[date_of_birth] ),
                   VAR AgeCalculated =
                       IF (
                           'Member'[date_of_birth] <= SelectedCutoff,
                           TRUNC ( YEARFRAC ( 'Member'[date_of_birth], SelectedCutoff ) )
                       )
                   RETURN
                       CONTAINS ( VALUES ( Age[Age] ), Age[Age], AgeCalculated )
               )
           ),
       COUNTROWS ( 'Member' )
   )

   (I modified the pattern slightly)

Note that this measure will return a simple count of Members if no Ages are filtered on.

Also, ages are only computed if date_of_birth <= Selected cutoff_date.

Here is a sample pbix demonstrating this.

https://www.dropbox.com/s/5p1faipgycwz5zo/Age%20Dynamic%20Segmentation.pbix?dl=1

Regards,

Owen