Dynamic Segmentation Issue

[jguercio]

Hi all,
I am struggling with implementing this pattern. In my case, I am using it to segment employees based on tenure in months. I am using the Event in Progress pattern called [Total Employee Tenure (Mo)] to dynamically determine tenure:

VAR MinDate = MIN( '_Calendar'[Date] )
VAR MaxDate1 = MAX(_Calendar[Date])
VAR MaxDate = IF( MaxDate1 > TODAY(), TODAY(), MaxDate1 )
VAR Tenure =
    CALCULATE (
        SUMX (
            FILTER (
                'FACT-Employees',
                'FACT-Employees'[Date Terminated] >= MinDate
                    || ISBLANK ( 'FACT-Employees'[Date Terminated] )
            ),
            DATEDIFF (
                'FACT-Employees'[Date Hired],
                IF (
                    OR (
                        ISBLANK ( 'FACT-Employees'[Date Terminated] ),
                        'FACT-Employees'[Date Terminated] >= MaxDate
                    ),
                    MaxDate,
                    'FACT-Employees'[Date Terminated]
                ),
                MONTH
            )
        ),
        REMOVEFILTERS ( _Calendar )
    )
RETURN
   IF( Tenure < 0, BLANK(), Tenure )

Here is the Dynamic Segmentation code:

VAR TenureInMonths = [Total Employee Tenure (Mo)]

VAR Bucketing =
    IF(
       HASONEVALUE( _Calendar[Fiscal Year] ),  
        VAR EmployeesInBucket =                
            FILTER(
                ALLSELECTED( 'FACT-Employees' ),
                VAR BucketOfEmployee =             
                    FILTER(                         
                        'Employee Tenure Buckets',
                        NOT ( ISBLANK( TenureInMonths ) )
                            && 'Employee Tenure Buckets'[MinTenure (Mo)] < TenureInMonths
                            && 'Employee Tenure Buckets'[MaxTenure (Mo)] >= TenureInMonths
                    )
                VAR IsEmployeesInSegments = NOT ( ISEMPTY( BucketOfEmployee ) )
                RETURN
                    IsEmployeesInSegments
            )
        VAR Result =
            CALCULATE(
                COUNTROWS( 'FACT-Employees' ),
                KEEPFILTERS( EmployeesInBucket )
            )
        RETURN
            Result
    )
RETURN
    Bucketing 

Here is the segment table:

The [Total Employee Tenure (Mo)] is behaving as expected:

But the results of the segmentation are not accurate. It is only showing the "More Than 5 Yrs" segment and the count is not accurate:

Any ideas for what I am doing wrong would be very much appreciated.

[v-shex-msft]

Hi @jguercio,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[johnt75]

Its the placement of the TenureInMonths variable. It is only being calculated once, at the very start, it isn't being calculated inside the FILTER iterating over employees. DAX variables are actually constants, not variables. If you move the variable definition inside the FILTER then it should work

Bucketing Measure =
VAR Bucketing =
    IF (
        HASONEVALUE ( _Calendar[Fiscal Year] ),
        VAR EmployeesInBucket =
            FILTER (
                ALLSELECTED ( 'FACT-Employees' ),
                VAR BucketOfEmployee =
                    FILTER (
                        'Employee Tenure Buckets',
                        VAR TenureInMonths = [Total Employee Tenure (Mo)]
                        RETURN
                            NOT ( ISBLANK ( TenureInMonths ) )
                                && 'Employee Tenure Buckets'[MinTenure (Mo)] < TenureInMonths
                                && 'Employee Tenure Buckets'[MaxTenure (Mo)] >= TenureInMonths
                    )
                VAR IsEmployeesInSegments =
                    NOT ( ISEMPTY ( BucketOfEmployee ) )
                RETURN
                    IsEmployeesInSegments
            )
        VAR Result =
            CALCULATE ( COUNTROWS ( 'FACT-Employees' ), KEEPFILTERS ( EmployeesInBucket ) )
        RETURN
            Result
    )
RETURN
    Bucketing

[jguercio]

I put in the code as suggested but no data is showing. I have fiscal year in the columns and Tenure Buckets in the rows, but no results show. Let me know what other information I can provide.

Coded Used:

Employee Tenure (% of GT) 2 = 
VAR Bucketing =
    IF (
        HASONEVALUE ( _Calendar[Fiscal Year] ),
        VAR EmployeesInBucket =
            FILTER (
                ALLSELECTED ( 'FACT-Employees' ),
                VAR BucketOfEmployee =
                    FILTER (
                        'Employee Tenure Buckets',
                        VAR TenureInMonths = [Total Employee Tenure (Mo)]
                        RETURN
                            NOT ( ISBLANK ( TenureInMonths ) )
                                && 'Employee Tenure Buckets'[MinTenure (Mo)] < TenureInMonths
                                && 'Employee Tenure Buckets'[MaxTenure (Mo)] >= TenureInMonths
                    )
                VAR IsEmployeesInSegments =
                    NOT ( ISEMPTY ( BucketOfEmployee ) )
                RETURN
                    IsEmployeesInSegments
            )
        VAR Result =
            CALCULATE ( COUNTROWS ( 'FACT-Employees' ), KEEPFILTERS ( EmployeesInBucket ) )
        RETURN
            Result
    )
RETURN
    Bucketing

Here is the relationship among the tables: