Dynamically scan for a single value in different path items of a hierarchy

[Anonymous]

Hello there, 

To implement role based security, I am in need of a variable which checks for a Division ID in all of its path items of Division ID & Parent Division ID hierarchy. So, the current code I have used is as below assuming the path length will not exceed 15.

DivisionID	parentDivisionid	Path_from_DAX
Div0	Div0	Div0
Div1	Div0	Div0|Div1
Div10	Div5	Div0|Div1|Div3|Div5|Div10
Div11	Div5	Div0|Div1|Div3|Div5|Div11
Div12	Div7	Div0|Div1|Div2|Div7|Div12
Div13	Div7	Div0|Div1|Div2|Div7|Div13
Div14	Div10	Div0|Div1|Div3|Div5|Div10|Div14
Div15	Div10	Div0|Div1|Div3|Div5|Div10|Div15
Div16	Div14	Div0|Div1|Div3|Div5|Div10|Div14|Div16
Div17	Div14	Div0|Div1|Div3|Div5|Div10|Div14|Div17
Div18	Div17	Div0|Div1|Div3|Div5|Div10|Div14|Div17|Div18
Div19	Div17	Div0|Div1|Div3|Div5|Div10|Div14|Div17|Div19
Div2	Div1	Div0|Div1|Div2
Div20	Div17	Div0|Div1|Div3|Div5|Div10|Div14|Div17|Div20
Div3	Div1	Div0|Div1|Div3
Div4	Div3	Div0|Div1|Div3|Div4
Div5	Div3	Div0|Div1|Div3|Div5
Div6	Div3	Div0|Div1|Div3|Div6
Div7	Div2	Div0|Div1|Div2|Div7
Div8	Div2	Div0|Div1|Div2|Div8
Div9	Div2	Div0|Div1|Div2|Div9

// the below variable selects the Division ID that is assigned to be given access to, to the logged in report user.

var vDivIDs=
SELECTCOLUMNS(
FILTER('Security_Org',[Employee ID]= username())
,"DivID",[Division ID]
)

// the below variable searches the division IDs returned in the above step in all the path items & returns all the division IDs it is related to. 

var vDivIDSets=
SELECTCOLUMNS(
FILTER('Division',
PATHITEM(Division[Path_from_DAX],1) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],2) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],3) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],4) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],5) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],6) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],7) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],8) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],9) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],10) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],11) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],12) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],13) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],14) in vDivIDs ||
PATHITEM(Division[Path_from_DAX],15) in vDivIDs
)
,"AllDivs",[DivisionID])

I then use the Division IDs from vDivIDSets to filter my fact table & achieve the required row level security.

Just wondering if in future, an extra level comes up in data & without the need of going back to the code to add another condition, is there a way to optimise it to work for any number of levels?

Something in the lines of - 

var x = MAX(PATHLENGTH(Path_from_DAX))

var n = GENERATESERIES(1,x) 

var vDivIDSets =  FILTER('Division', PATHITEM(Division[Path_from_DAX],1) in vDivIDs || FILTER('Division', PATHITEM(Division[Path_from_DAX],1) in vDivIDs  || ......... FILTER('Division', PATHITEM(Division[Path_from_DAX],x) in vDivIDs 

Cheers,

Pravallika

[Anonymous]

@Greg_Deckler  Thanks a ton!

[Greg_Deckler]

@Anonymous Use PATHCONTAINS instead: PATHCONTAINS function (DAX) - DAX | Microsoft Docs

[Anonymous]

Hi @Greg_Deckler , thanks for your response, but how can we pass multiple values to pathcontains function, as it expects only one value.

For example,  we are to pass pathcontains(path, vDivIDs) .. if vDivIDs return just one division, say D2 this will work straight, vDivIDs can also return multiple divisions.

PATHCONTAINS and Multiple selected value - Microsoft Power BI Community

Have gone through this, once the columns are created, it looks like the equivalent of above code. How to then search for the division IDs in those columns created for each path item.. also, there is no aggregation in need, just a search through all the individual columns generated for each path item..

Any inputs would be of great help!

Cheers,

Pravallika

[Greg_Deckler · Accepted solution]

@Anonymous Ah, then how about this?

VAR __Path = Division[Path_from_DAX]
VAR __PathLength = PATHLENGTH(__Path)
VAR __PathTable = 
  ADDCOLUMNS(
    GENERATESERIES(1,__PathLength,1),
    "Division ID",PATHITEM(__Path,[Value])
  )
RETURN
  INTERSECT(vDivIDs,__PathTable)

Basically, turn the path into a table and find the division ids that match.