Distinct Values in T1 not in T2 with bi-directional slowly changing dimension

[gftx90]

Hello PBI community,

I am so stuck.

I have an overtime model which contains the following tables

1. Fact Overtime - recieves daily instances of overtime
2. DimTeamMember - type 2 changing dimension, updates with changes of attributes. most current has IsActive value of "Yes"
3. DimTeamMemberList - distinct list of team members to resolve the M:N between tables 1 and 2
4. Dim Date

All visible columns are keys.

I am having a lot of trouble getting a count of active team members that are in table 2 but not table 1 (the ones that did not work overtime), especially when grouped by their respective division. I have managed to create a measure that works as a stand alone calculation with this code...

TEST = 
VAR A = MIN('DimDate'[Date])
VAR B = 
CALCULATETABLE(
    VALUES('DimTeamMember'[EmployeeSK]), 
    CROSSFILTER('DimTeamMember'[EmployeeSK],'DimTeamMemberList'[EmployeeSK], NONE),
    'DimTeamMember'[IsActive] = "Yes", 
    OR('DimTeamMember'[TerminationDate] >= a, 'DimTeamMember'[TerminationDate] = blank())
    )
VAR C = CALCULATETABLE(VALUES('FactOvertime'[EmployeeSK]))
VAR D = COUNTROWS( DISTINCT(EXCEPT(B, C)))
RETURN D 

but this measure falls short as I cant break down this total by division. here is an example.

I believe the way the model relationships are set up is necessary for other aspects of reporting because the historical team member dimension is a special case with many attributes.

Is there a way to solve this with dax? 

[Anonymous · Accepted solution]

Hi @gftx90 ,

I created a sample pbix file(see attachment), please check whether that is what you want. I updated the formula of your measure [TEST] as below:

TEST = 
VAR _curdate =
    SELECTEDVALUE ( 'DimDate'[Date] )
VAR _tmembers =
    CALCULATETABLE (
        VALUES ( 'DimTeamMember'[EmployeeSK] ),
        FILTER (
            'DimTeamMember',
            'DimTeamMember'[IsActive] = "Yes"
                && (
                    ISBLANK ( 'DimTeamMember'[TerminationDate] )
                        || IF (
                            NOT ( ISBLANK ( 'DimTeamMember'[TerminationDate] ) ),
                            'DimTeamMember'[TerminationDate] >= _curdate
                        )
                )
        )
    )
VAR _otmembers =
    CALCULATETABLE ( VALUES ( 'FactOvertime'[EmployeeSK] ) )
VAR _ncount =
    COUNTROWS ( DISTINCT ( EXCEPT ( _tmembers, _otmembers ) ) )
RETURN
    _ncount

Best Regards

[amitchandak]

@gftx90 , Refer if the HR blog's current employee logic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...