How to filter data that is not required in a power pivot table?

[Mavericks334]

Hi,

I have created a power pivot table with the below details [TABLE="width: 386"]
<COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 179pt; mso-width-source: userset; mso-width-alt: 8740" width=239><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204" width=197><TBODY>[TR]
[TD="width: 79"]Site ID[/TD]
[TD="width: 239"]Supervisior[/TD]
[TD="width: 197"]Employee Name[/TD]
[/TR]
</TBODY>[/TABLE]


the information i get here is from a few measures that i created, which works fine. When i add a rank field or Total working days it adds all the names of the people in the employees table, also it adds them under all 3 sites that i have.

how to rectify this problem. Below are the measures that i have written

Target Measure: =CALCULATE(Sumx('Employee Details',RELATED(Tenure[Target])))
Rank Measure: =(RANKX(All('Employee Details'),'SharePoint Data'[%Productivity], ,0))

I am assuming because i am using employee details it is giving me the total list of employees, who do i get the result for the rank or workingdays for the employees that in above calculated measure.

 

[Laurent C]

Interesting discovery. This seems to be due to the way RANKX works when the value is not found in the provided column.

As a work around, you might want to nest your rank expression in an IF function
IF( yourmeasure , yourexpression , BLANK())

 

[Mavericks334]

i added a new column with the sites name in the employee table, avoided that problem and then used and if with blank to avoid the rest.