I have HR Power Pivot Data imported and organized like this:
我有HR Power Pivot数据导入和组织如下：
Division (same value for all), Branch (7 different), UNIQUEID, STATUS (takes on values OUTLOAN, INLOAN or blank)
除法（所有值相同），分支（7种不同），UNIQUEID，STATUS（取值OUTLOAN，INLOAN或空白）
If an employee has been transferred, there will be two rows with the same UNIQUEID. One with STATUS OUTLOAN (home position) and another one with STATUS INLOAN (new position).
如果已转移员工，则将有两行具有相同的UNIQUEID。一个带有STATUS OUTLOAN（起始位置），另一个带有STATUS INLOAN（新位置）。
I need to find the number of people per branch that has been transferred in from outside of the branch (INLOAN), preferrably using DAX and pivottables.
我需要找到从分支机构（INLOAN）外部传输的每个分支的人数，最好使用DAX和pivottables。
Just to clarify, the people who have moved positions WITHIN a branch should not be counted, just the ones that are on INLOAN from an external branch.
只是为了澄清，那些在分支机构中移动位置的人不应该被计算在内，而只是那些来自外部分支机构的INLOAN。
3 个解决方案
#1
0
Here is a simple way to achieve what you want. Create a measure:
这是一种实现您想要的简单方法。创建一个度量：
EmployeeCount: = COUNTROWS('HR Data')
Then add that measure to a pivot table in Excel and then add the STATUS column to the filter area of the pivot table and filter on "INLOAN". Add the BRANCH column to the row headers of the pivot table.
然后将该度量添加到Excel中的数据透视表，然后将STATUS列添加到数据透视表的过滤器区域，并过滤“INLOAN”。将BRANCH列添加到数据透视表的行标题中。
This will tell you how many people in each branch are on loan from another branch.
这将告诉您每个分支机构中有多少人从另一个分支机构借款。
#2
0
So,
所以，
Previously, I had calculated the number of duplicate UNIQUEID per branch (ie. the number of internal transfers per branch) as the count of UNIQUEID less the distinct number of UNIQUEID per branch. If one subtracts this from the calculation given by mendosi one should arrive at the number of transfers per branch that are not internal.
以前，我计算了每个分支的重复UNIQUEID数（即每个分支的内部传输数），作为UNIQUEID的计数减去每个分支的UNIQUEID的不同数量。如果从mendosi给出的计算中减去这个，则应该得出每个分支不是内部的转移数。
#3
0
The function you are looking for is DISTINCTCOUNT
您正在寻找的功能是DISTINCTCOUNT
The below would give you the distinct count of employees in each branch by counting the UNIQUEID fields unique values.
下面将通过计算UNIQUEID字段的唯一值，为您提供每个分支中不同的员工数。
Count:= DISTINCTCOUNT( table_name[UNIQUEID])
To expand upon it is the helpful CALCULATE statement, the below would do a distinct count of the UniqueID, but only on the rows which have status = Inloan
要扩展它是有用的CALCULATE语句，下面将对UniqueID进行不同的计数，但仅限于status = Inloan的行
Count:= CALCULATE( DISTINCTCOUNT( table_name[UNIQUEID]), table_name[STATUS] = "INLOAN")
#1
0
Here is a simple way to achieve what you want. Create a measure:
这是一种实现您想要的简单方法。创建一个度量：
EmployeeCount: = COUNTROWS('HR Data')
Then add that measure to a pivot table in Excel and then add the STATUS column to the filter area of the pivot table and filter on "INLOAN". Add the BRANCH column to the row headers of the pivot table.
然后将该度量添加到Excel中的数据透视表，然后将STATUS列添加到数据透视表的过滤器区域，并过滤“INLOAN”。将BRANCH列添加到数据透视表的行标题中。
This will tell you how many people in each branch are on loan from another branch.
这将告诉您每个分支机构中有多少人从另一个分支机构借款。
#2
0
So,
所以，
Previously, I had calculated the number of duplicate UNIQUEID per branch (ie. the number of internal transfers per branch) as the count of UNIQUEID less the distinct number of UNIQUEID per branch. If one subtracts this from the calculation given by mendosi one should arrive at the number of transfers per branch that are not internal.
以前，我计算了每个分支的重复UNIQUEID数（即每个分支的内部传输数），作为UNIQUEID的计数减去每个分支的UNIQUEID的不同数量。如果从mendosi给出的计算中减去这个，则应该得出每个分支不是内部的转移数。
#3
0
The function you are looking for is DISTINCTCOUNT
您正在寻找的功能是DISTINCTCOUNT
The below would give you the distinct count of employees in each branch by counting the UNIQUEID fields unique values.
下面将通过计算UNIQUEID字段的唯一值，为您提供每个分支中不同的员工数。
Count:= DISTINCTCOUNT( table_name[UNIQUEID])
To expand upon it is the helpful CALCULATE statement, the below would do a distinct count of the UniqueID, but only on the rows which have status = Inloan
要扩展它是有用的CALCULATE语句，下面将对UniqueID进行不同的计数，但仅限于status = Inloan的行
Count:= CALCULATE( DISTINCTCOUNT( table_name[UNIQUEID]), table_name[STATUS] = "INLOAN")