Current value in an iterator for a table variable

[DAX_merchant]

Dear DAX experts,

I am trying to calculate the rank of an employee compared to other employees using the RANKX function. The employee table is NOT linked to the location table. There is a table that has relationships going from location and employee table, this contains sales, and we can calculate various measures from the data in that table. 

Employees can work in several stores and several regions, this is causing some problems. 

The functionality that I would like: I have slicers for region, store type, store (these three are from the location table) and then employee type from the employee table. I also have a slicer for employee. I want to be able to rank an employee based on a group that they don't necessarily belong to. The problem arises that the filters from the location table will alter the result of measures from the sales table. 

For example, I select all employees in the northern region, but there are some of these employees that operate in the southern region as well. The sales measure will only calculate based on the rows in the sales table that happen in the northern region, this is what I expect. But of course, calculating sales for an employee that does not exist in the northern region is impossible without some sort of ALL statement on the location table, since they would not be contained in the rows of the filtered sales table. 

Now, my rank function looks something like this:

CALCULATE(RANKX(table_var, [sales], , DESK, SKIP), employee_name = employee)

employee = SELECTEDVALUE(employee_name) // this is a disconected slicer of employee name, probably unnecessary, but makes things a little easier right now

table_var = a calculatedtable of one column, employee_name, created using summarize from the original employee table with a filter to include the extra employee we are trying to look at, if they don't fit into the current comparison group. 

As you can see, [sales] will evalute to 0 if the employee is being filtered out of the sales table due to location filters, but if we do:

CALCULATE([sales], ALL(location)) then employees that work in other locations will have their agregate sales, rather than their sales for only specified location. 

I want something along the lines of:

IF(current value of table_var from RANKX iteration = selectedvalue(disconnected employee slicer), CALCULATE([sales], ALL(location), [sales])

And have this as what the RANKX function ranks by. 

But I can't seem to figure out how to reference the current value of table_var within RANKX. 

Sorry for the long explanation, but I figure the context will be helpful. 

[v-kongfanf-msft]

Hi @DAX_merchant ,

You can try the following to find the ranking result dynamically by combining selectedvalue with a virtual table.

Rank by Sales = 
VAR SummaryTable =
    SUMMARIZE (
        FILTER (
            ALL(EmployeeSales),
            EmployeeSales[Region] = SELECTEDVALUE(Location[Region])
                && EmployeeSales[StoreType] = SELECTEDVALUE(Location[StoreType])
                && EmployeeSales[Store] = SELECTEDVALUE(Location[Store])
        ),
        EmployeeSales[EmployeeName],
        "TotalSales", SUM ( EmployeeSales[Sales] )
    )
return RANKX(SummaryTable,[sum_])

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DAX_merchant]

Hey @v-kongfanf-msft,

Thank you for the response, I appreciate it. This will not work as like I have described above, setting these filters individually will remove some employees that arent found in those regions or stores or store types. 

I need to be able to rank an employee that would otherwise be filtered out from the sales table when applying the filters from the location table. 

From your example, what if there was an employee slicer, and you select an employee, Erik who is in the northern region. The filter on Southern region will remove him from this measure you have written, but I want him included in the rank, even if he is not in the Southern region. And to do this, you need to use calculate around whatever measure we use to rank the employees, since in that context just calling the measure will return 0 for the employee not inherently in the group. 

I can see though that you are creating a column within the summarize function. Can this column be called in the rank function? Really, I am just looking for a way to extract the current value of a column in row context when using an iterator.