Power BI Measure to filter based on UPN related attribute - not filtering properly

Question

I am trying to create a measure that will sum the Cost for a person using the report as well as any cost from employees that work for them directly, or work for managers that work for them.

My data has an EmpID in the fact table, and I have a dimension table with the EmpID and its associated email which is their UPN. This calculation results in the EmpID of 3 when using View as Role of emp3@email.com in my test:

Logged In EmpID = CALCULATE(MAX(dim_Employee[EmpID]),USERPRINCIPALNAME()=dim_Employee[UPN])

I have another measure that is measuring the cost using calculate and a filter for the EmpID of the person's UPN or where that id is in the Manager or Director field.

myCost = CALCULATE(SUM(fact_Cost[Cost]),
FILTER(dim_Employee,
[EmpID]=[Logged In EmpID]
||dim_Employee[MgrID]=[Logged In EmpID]
||dim_Employee[DirectorID]=[Logged In EmpID]
)
)

Using view as role of emp3@email.com, which is the EmpID 3, this should result in $125. However the result showing is only $15 which is where the EmpID is 3, so it looks like its not picking up the Mgr and DirID part of the filter.

This is the test fact table, with the related dimensions I am filtering on highlighted with the values I expect to catch with the filter when using view as role:

This is the dimension table with the employee relationship to the managers and directors:

UPDATE: Clarifying the visual that should show the $125 is one where I don't have any other data in it:

I tried the ALL weaved into the filter per first comment below and I still don't get beyond $15.

When I reduce the formula to just the Manager or just the Director, I get a blank. So it is not able to get me records where the [Logged In EmpID] = [MgrID] or [DirectorID]

Manager only where I get blank:
    myCost just Manager = 
    CALCULATE(SUM(fact_Cost[Cost]),
            FILTER(dim_Employee,
                dim_Employee[MgrID]=[Logged In EmpID]
                )
            )

and tried with the ALL:

   myCost Manager ALL = 
    CALCULATE (
        SUM ( fact_Cost[Cost] ),
        FILTER (
            ALL(dim_Employee[MgrID]),
                dim_Employee[MgrID] = [Logged In EmpID]
               )
            )

Any ideas what I am doing wrong, or an alternative approach? Thanks.

Answer 1

The reason it's showing only cost for one Employee: in your visual, you have Employees, Managers and Directors on the rows. The rows serve as filters. So, you current formula essentially does the following (I will use 3rd row as example):

• Filter table "fact_Cost" for the records where Employee = 3, Manager = 6 and Director = 7 (this filter request comes from your visual). Result: one record with this combination of Ids.
• Filter this record further for Employee ID=3, OR Manager_ID=3, OR Director_ID=3. First criteria is True, others are false (because 6 <> 3, 7 <> 3). But the record stays.
• Remaining record filters table with costs and sums them up. Since we have only 1 record, it shows its cost - 15.

Solution: Before applying your "OR" criteria, you need to "remove" filters that are coming from your visual, so that instead of 1 record you can "see" them all. For example:

myCost =
VAR Login_ID = [Logged In EmpID]
RETURN
CALCULATE (
    SUM ( fact_Cost[Cost] ),
    FILTER (ALL(dim_Employee[EmpID],dim_Employee[MgrID],dim_Employee[DirectorID]),
        dim_Employee[EmpID] = Login_ID ||
        dim_Employee[MgrID] = Login_ID ||
        dim_Employee[DirectorID] = Login_ID
    )
)

Here, we first remove all filters from the columns we need to access in dim Employee. Then we re-filter these columns as needed, and apply resulting set of records to the cost table.

Edit:

The above measure works correctly. To test it, replace your [Logged In EmpID] measure with a specific Employee ID, i.e, 3:

    myCost =
    VAR Login_ID = 3
    RETURN
    CALCULATE (SUM ( fact_Cost[Cost] ), 
    FILTER (ALL(dim_Employee[EmpID],dim_Employee[MgrID],dim_Employee[DirectorID]),
            dim_Employee[EmpID] = Login_ID ||
            dim_Employee[MgrID] = Login_ID ||
            dim_Employee[DirectorID] = Login_ID
        )
    )

Result I've got in your pbix file:

So, the only reason you are getting blanks then must be [Logged In EmpID] measure - it's not returning "3" as expected. Which means that userprincipalname function is not returning a matching string for dim_Employee[UPN], in other words, this part of your formula:

USERPRINCIPALNAME()=dim_Employee[UPN]

is not working as intended.

I have no way to test this part on a desktop Power BI, because I don't see your logins. But you can easily do it yourself: create a measure that shows user name:

User Name = USERPRINCIPALNAME()

and carefully compare its result with the content of dim_Employee[UPN] column. My guess is that they won't match exactly. Once you figure out how to make them identical, the formula will work.