DAX Formula filter propagation elementary question  [Solved]

Anything related to PowerPivot and DAX Formuale
Posts: 3
Joined: Wed Feb 12, 2020 11:11 pm

DAX Formula filter propagation elementary question

Postby nhl66pens » Wed Feb 12, 2020 11:27 pm

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.

The initial Diagram View from inside PowerPivot is in attachment (HRDiagramView.png).
The DAX formula and resulting display in the pivot table is in attachment (COUNT screenshot.png).
The last attachement (SQL Select that works.JPG) is a screen shot of a SQL Select statement that produces the results I'm trying to get in Excel.

I know it has to do with the Filter not propagating from the Department table to the DepartmentHistory table to the Employee table but I don't know how to force it to alter the relationship. Of course, there's always the chance that I'm completely off base and there is a simpler reason that I just don't know about.

Thanks for any help you can provide.

SQL Select that works.JPG
SQL Select that works.JPG (81.18 KiB) Viewed 339 times
COUNT screenshot.png
COUNT screenshot.png (57.45 KiB) Viewed 339 times
HRDiagramView.png (68.16 KiB) Viewed 339 times

Posts: 3
Joined: Wed Feb 12, 2020 11:11 pm

Re: DAX Formula filter propagation elementary question  [Solved]

Postby nhl66pens » Fri Feb 14, 2020 11:20 pm

For anyone having a similar issue, someone on the Microsoft forum provided an answer.

= CALCULATE(COUNT('HumanResources EmployeeDepartmentHistory'[BusinessEntityID]))

Since I'm new to this, I can't really say this for certain, but I believe this will just cause a problem trying to do something else with the relations between these tables that would be resolved in SQL by using Joins. But, at least I know now that you cannot alter the direction of the 1 to many relationships in case anyone else just starting was wondering.

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests