locked
DAX Formula filter propagation elementary question RRS feed

  • Question

  • This should be a simple question because I am new to DAX and PowerPivot. I was using the AdventureWorks 2017 sample database and wanted to show my current employer some of the analytical data I could provide them and hit a snag almost right away. I was trying to simply do a count of employees based by Department Name.

    I think the problem is the relationship is 1 to many in the wrong direction but I could not get it to change the direction. There are 3 tables involved. HumanResources Employee, HumanResources EmployeeDepartmentHistory and HumanResources Department. I think it should be 1 to many with 1 being HumanResources EmployeeDepartmentHistory and the many being HumanResources Employee.

    I just wanted to show a COUNT by Department of the current Employees.

    The initial diagram view from the Power Pivot.

    Here is the COUNT formula I am trying to implement.

    Here is a screen shot of a SQL Select statement that reflects what I'm trying to accomplish with DAX.

    Wednesday, February 12, 2020 3:27 PM

Answers

  • So the filters propagate in the direction of the arrows on the relationships. So the Department filters the History table, but the History table does not filter the employee table. But you cannot usually just arbitrarily change the direction of a relationship. I'm guessing from the names that for a single employee they can have multiple history records if they move between various departments.

    So in this case the History table is the thing you should be reporting on, so changing your measure to the following should get you the answer you are after.

    =DISTINCTCOUNT( 'HumanResources EmployeeDepartmentHistory'[BusinessEntityID] )

    However because your History table has a Start and End date range you might need to extend this measure to something like the following which will filter the history table based on the selected dates in the calendar table.

    = CALCULATE(
    DISTINCTCOUNT( 'HumanResources EmployeeDepartmentHistory'[BusinessEntityID] )
    , FILTER( 'HumanResources EmployeeDepartmentHistory'
        , 'HumanResources EmployeeDepartmentHistory'[StartDate] <= MAX('Calendar'[Date] )
      &&  'HumanResources EmployeeDepartmentHistory'[EndDate] >= MIN('Calendar'[Date] ) 
    )


    http://darren.gosbell.com - please mark correct answers

    Wednesday, February 12, 2020 10:15 PM

All replies

  • So the filters propagate in the direction of the arrows on the relationships. So the Department filters the History table, but the History table does not filter the employee table. But you cannot usually just arbitrarily change the direction of a relationship. I'm guessing from the names that for a single employee they can have multiple history records if they move between various departments.

    So in this case the History table is the thing you should be reporting on, so changing your measure to the following should get you the answer you are after.

    =DISTINCTCOUNT( 'HumanResources EmployeeDepartmentHistory'[BusinessEntityID] )

    However because your History table has a Start and End date range you might need to extend this measure to something like the following which will filter the history table based on the selected dates in the calendar table.

    = CALCULATE(
    DISTINCTCOUNT( 'HumanResources EmployeeDepartmentHistory'[BusinessEntityID] )
    , FILTER( 'HumanResources EmployeeDepartmentHistory'
        , 'HumanResources EmployeeDepartmentHistory'[StartDate] <= MAX('Calendar'[Date] )
      &&  'HumanResources EmployeeDepartmentHistory'[EndDate] >= MIN('Calendar'[Date] ) 
    )


    http://darren.gosbell.com - please mark correct answers

    Wednesday, February 12, 2020 10:15 PM
  • Thanks Darren!
    Thursday, February 13, 2020 1:22 PM