Dax to Indentify Sales Manager

Hi James__ 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Hi James__ 

I think I may need more context. From the given information, if the salesperson id is invalid you would like to fetch the corresponding manager's id. If we assume that based on the manager of the department we can do this allocation. Then, try this formula:

EffectiveSalespersonID =
IF(
    Sales[IsValid] = 1,
    Sales[SalespersonID],
    LOOKUPVALUE(
        'Sales People'[SalespersonID],
        'Sales People'[DepartmentID], Sales[DepartmentID],
        'Sales People'[Role], "Manager"
    )
)

If we assumen Manager id of department is present, then:

EffectiveSalespersonID =
IF(
    Sales[IsValid] = 1,
    Sales[SalespersonID],
    LOOKUPVALUE(
        'Sales People'[ManagerID],
        'Sales People'[DepartmentID], Sales[DepartmentID]
    )
)

Again, more context will be needed to give the exact formula and decide on how to retrieve the manager id, if this makes sense then you could swap the corresponding fields.

Hello James__ 

Suppose i have 'Sales' and 'Sales People' that look like 

A calcualted column with LOOKUPVALUE() would give me the result so that invalid SalespersonIDs get automatically reassigned to the manager of the department. (Fyi, for this solution with LOOKUPVALUE(), no relationship required between these two tables) 

Hope this helps:) 

Assuming you have these fields:

Sales[SalespersonID]

Sales[ValidID] (1 = valid, 0 = invalid)

Sales[Department]

Sales People[SalespersonID]

Sales People[Department]

Sales People[IsManager] (1 = manager, 0 = regular salesperson)

You can write a calculated column in the Sales table:

AssignedPersonID =
IF (
Sales[ValidID] = 1,
Sales[SalespersonID],
LOOKUPVALUE (
'Sales People'[SalespersonID],
'Sales People'[Department], Sales[Department],
'Sales People'[IsManager], 1
)
)

How it works

If valid, return the entered salesperson.

If invalid, LOOKUPVALUE finds the manager’s ID in the same department from the Sales People table.

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

Hi James__ 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you