Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jguercio
Frequent Visitor

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:

93397de0426ac1b81a4f6665b2c7b1c70d0975c8ff18cfd162983f0c679d6765

 

 

 

 

 

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

85d3925e740827dc5d71bfcce037e766491059ec45ee505a55ecb1def2bb1a9b

 

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:

3fec41efc96def82ea4cff1fcbad64da415043aa7f5f517dc2d9e4db14e11f73

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

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
johnt75
Super User
Super User

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

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:

Screenshot 2022-09-19 201255.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors