pathitems
1 Topic
Dynamically scan for a single value in different path items of a hierarchy
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, PravallikaSolved1.4KViews0likes4Comments