Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic Age Calculation Based on User Selected Cutoff Date

I have a customer table that includes a date of birth field; our customer table also includes children of customers.   My goal is to create a visual that allows one to select an arbitrary date in t...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Still struggling with this; in a nutshell what I think I need is a way to create a calculated column in a table that will respond to a selected filter context from the report page. 

 

I've tried numerous ways (admitedly blindly) and can't seem to get there (none of the tries below worked):

 

age_at_date_selected_trial1 =
CALCULATE (
    IF (
        NOT ( ISBLANK ( MAX ( 'Member'[date_of_birth] ) ) ),
        TRUNC (
            YEARFRAC (
                MAX ( 'Member'[date_of_birth] ),
                SELECTEDVALUE ( 'Cutoff'[cutoff_date] )
            )
        )
    )
)

 

age_at_date_selected_trial2 =
CALCULATE (
    IF (
        NOT ( ISBLANK ( MAX ( 'Member'[date_of_birth] ) ) )
            && HASONEVALUE ( 'Cutoff'[cutoff_date] ),
        TRUNC (
            YEARFRAC (
                MAX ( 'Member'[date_of_birth] ),
                SELECTEDVALUE ( 'Cutoff'[cutoff_date] )
            )
        )
    )
)
age_at_date_selected_trial3 =
CALCULATE (
    IF (
        NOT ( ISBLANK ( MAX ( 'Member'[date_of_birth] ) ) )
            && HASONEVALUE ( 'Cutoff'[cutoff_date] ),
        TRUNC (
            YEARFRAC ( MAX ( 'Member'[date_of_birth] ), VALUES ( 'Cutoff'[cutoff_date] ) )
        )
    )
)

If anyone can nudge me in the right direction I'd be really grateful.

 

 

OwenAuger's avatar
OwenAuger
Super User
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    OwenAuger

     

    Wow, that's amazing, can't thank you enough for the insight and assistance. I'm working to digest the Dax patterns link you included as best I can. The CONTAINS() function was new to me and is obviously a key component of this measure. 

     

    I did have to edit just a little to adjust (see below), but there's no way this would have happened without your invaluable input. Thanks again!

     

    Dependent Count by Age Based on Selected Cutoff = 
IF (
    ISFILTERED ( Age[Age] ),
    VAR SelectedCutoff =
        MAX ( Cutoff[cutoff_date] )
    RETURN
        CALCULATE (
            COUNTROWS ( 'Member' ),
            FILTER ( 'Member', 'Member'[is_active_dep] ),
            FILTER (
                VALUES ( 'Member'[date_of_birth] ),
                VAR AgeCalculated =
                    IF (
                        NOT ( ISBLANK ( 'Member'[date_of_birth] ) )
                            && 'Member'[date_of_birth] <= SelectedCutoff,
                        TRUNC ( YEARFRAC ( 'Member'[date_of_birth], SelectedCutoff ) )
                    )
                RETURN
                    CONTAINS ( VALUES ( Age[Age] ), Age[Age], AgeCalculated )
            )
        ),
    COUNTROWS ( 'Member' )
)

     

    v-yulgu-msft

     

    Thank you for response and assistance, I really do appreciate it.