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 Tenu...
johnt75's avatar
johnt75
Super User
3 years ago

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
3 years ago

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: