Forum Discussion

jguercio's avatar
jguercio
Frequent Visitor
3 years ago

Dynamic Segmentation Issue

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.

3 Replies

  • 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's avatar
      jguercio
      Frequent Visitor

      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:

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    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