MDS Override Dax Query

[Charinite]

Hi,

One of my ex-colleague designed the below DAX query for the access override roles in the analysis service to provide additional access to the selected colleagues based on the superior they have access to. We recently realized that DAX is only returning two override values (MAX &MIN) and is skipping all the others leading to limiting the override access for a particular colleague to only 2 even if that colleague has permission to override more than two.

DAX:(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MAXX(FILTER(HR_Supervisory_Org,HR_Supervisory_Org[LoginId] = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)
||
(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MAXX(FILTER(HR_Supervisory_Org,RELATED(Sup_Org_RLS_Override[Business Manager 1 Login Id]) = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)
||
(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MAXX(FILTER(HR_Supervisory_Org,RELATED(Sup_Org_RLS_Override[Business Manager 2 Login Id]) = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)
||
(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MAXX(FILTER(HR_Supervisory_Org,RELATED(Sup_Org_RLS_Override[Business Manager 3 Login Id]) = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)
||
(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MINX(FILTER(HR_Supervisory_Org,RELATED(Sup_Org_RLS_Override[Business Manager 1 Login Id]) = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)
||
(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MINX(FILTER(HR_Supervisory_Org,RELATED(Sup_Org_RLS_Override[Business Manager 2 Login Id]) = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)
||
(PATHCONTAINS(
HR_Supervisory_Org[Path*],
MINX(FILTER(HR_Supervisory_Org,RELATED(Sup_Org_RLS_Override[Business Manager 3 Login Id]) = USERNAME()),HR_Supervisory_Org[Colleague ID])
)
)

I have tried distinct and rankx but it seems I'm not using it properly and hence is giving error.

Please help me in getting the issue fixed.

The goal is to get all the colleague ID whose access is permitted to the one who is logging in instead of getting just the highest colleague id and lowest colleague id which is happening now.

Please let me know if I need to add more info.

[technolog · Accepted solution]

The current DAX formula is using MAXX and MINX which are only returning the highest and lowest values respectively. To get all the colleague IDs for which the logged-in user has access, you'll need to modify the DAX formula.

Instead of using MAXX and MINX, you should use a combination of FILTER and CONTAINSROW to check if the current row's path contains any of the colleague IDs that the logged-in user has access to.

Here's a way to approach this:

First, create a table of all the colleague IDs that the logged-in user has access to:

ColleagueIDsTable =
FILTER(
HR_Supervisory_Org,
HR_Supervisory_Org[LoginId] = USERNAME() ||
RELATED(Sup_Org_RLS_Override[Business Manager 1 Login Id]) = USERNAME() ||
RELATED(Sup_Org_RLS_Override[Business Manager 2 Login Id]) = USERNAME() ||
RELATED(Sup_Org_RLS_Override[Business Manager 3 Login Id]) = USERNAME()
)
Now, for each row in HR_Supervisory_Org, check if the path contains any of the colleague IDs from the ColleagueIDsTable:

FinalMeasure =
SUMX(
HR_Supervisory_Org,
IF(
CONTAINSROW(
ColleagueIDsTable,
HR_Supervisory_Org[Colleague ID]
),
1,
0
)
)
This FinalMeasure will give you a count of all the rows in HR_Supervisory_Org where the path contains a colleague ID that the logged-in user has access to. If you want to display the actual colleague IDs, you can use the ColleagueIDsTable in a table visual.