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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Egemeny
Frequent Visitor

Employee Retention, Filtering Based on Job Title and Business Location

Hi everyone,

 

Thanks to this group I managed to work on my dax to calculate retention % for the business. However, the problem that I'm experiencing is when I tried to filter my visualization based on Job Title or Business Unit code, my measure doesn't change in line with the filter.

My aim is to see retention % based on different business units and job title's so we can identify possible challenges for retaining talent within the organization.

 

Here is my DAX:

Thank you so much for the support!

Retention % = 
VAR _Previous12START =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -13 ) + 1
VAR _Previous12END =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -1 )
VAR _LEFT =
    CALCULATE (
        COUNT ( 'Master Data'[Employee Code] ),
        FILTER (
            ALL ( 'Master Data' ),
            'Master Data'[Date Engaged] >= _Previous12START
                && 'Master Data'[Date Engaged] <= _Previous12END
                && 'Master Data'[Date of Resignation] >= _Previous12START
                && 'Master Data'[Date of Resignation] <= _Previous12END
        )
    ) + 0
VAR _Total =
    CALCULATE (
        COUNT ( 'Master Data'[Employee Code] ),
        FILTER (
            ALL ( 'Master Data' ),
            'Master Data'[Date Engaged] <= _Previous12END
                && OR (
                    'Master Data'[Date of Resignation] >= _Previous12START,
                    'Master Data'[Date of Resignation] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE ( _LEFT, _Total )

 

This is how the visualization look like:

Retention.JPG

 

 

1 ACCEPTED SOLUTION
Shaurya
Memorable Member
Memorable Member

Hi @Egemeny,

 

You are using Filter All which is why your measure is not working in the context of your slicers. Removing all should do the trick.

 

Retention % = 
VAR _Previous12START =
    EOMONTH (MAX('Calendar'[Date]),-13)+1
VAR _Previous12END =
    EOMONTH (MAX('Calendar'[Date]),-1 )

VAR _LEFT =
    CALCULATE (
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] >= _Previous12START
                && 'Master Data'[Date Engaged] <= _Previous12END
                && 'Master Data'[Date of Resignation] >= _Previous12START
                && 'Master Data'[Date of Resignation] <= _Previous12END
        )
    ) + 0

VAR _Total =
    CALCULATE(
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] <= _Previous12END
                && OR (
                    'Master Data'[Date of Resignation] >= _Previous12START,
                    'Master Data'[Date of Resignation] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE(_LEFT, _Total)

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

View solution in original post

1 REPLY 1
Shaurya
Memorable Member
Memorable Member

Hi @Egemeny,

 

You are using Filter All which is why your measure is not working in the context of your slicers. Removing all should do the trick.

 

Retention % = 
VAR _Previous12START =
    EOMONTH (MAX('Calendar'[Date]),-13)+1
VAR _Previous12END =
    EOMONTH (MAX('Calendar'[Date]),-1 )

VAR _LEFT =
    CALCULATE (
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] >= _Previous12START
                && 'Master Data'[Date Engaged] <= _Previous12END
                && 'Master Data'[Date of Resignation] >= _Previous12START
                && 'Master Data'[Date of Resignation] <= _Previous12END
        )
    ) + 0

VAR _Total =
    CALCULATE(
        COUNT('Master Data'[Employee Code]),
        FILTER('Master Data',
            'Master Data'[Date Engaged] <= _Previous12END
                && OR (
                    'Master Data'[Date of Resignation] >= _Previous12START,
                    'Master Data'[Date of Resignation] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE(_LEFT, _Total)

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors