Dax code for row level security to handle path function where a parent can report to a parent

[Doylezeebeast]

Hi there,

              I have been making use of the "PATH" function in power BI to build some row level security. This is for a data set where this lists all of the supervisory organizations in a business, along with its reference ID, who the mananger is, their email address and then the reference ID number of the "superior" supervisory organization. However in some cases there can be a sceniro where by a manager can inherit the supervisory organization below them due to the supervisory organization not having a “real” manager in post. In these cases the dax for the row level security appears to break and then no visuals are displayed on the screen.

When I look at the “PATH” function in my table this works correctly and builds all the way to the top of the hierarchy. So I think it must be something to do with the Dax used for the RLS? For reference here is the code being used:

PATHCONTAINS(

    Data[Path],

    Maxx(

        FILTER(

            Data,

            [Email] = USERPRINCIPALNAME()

        ),

        Data[Supervisory Org reference ID]

    )

)

Any help would be greatly received! Thank you

[Doylezeebeast]

Is anyone able to help with this please?

[parry2k]

@Doylezeebeast can you share the sample pbix file with the expected output and will get back to you with a solution. 

[Doylezeebeast]

@parry2k Thanks for your input on this below are the links to the PBIX and excel file. I have put some comments in the PBIX about the excepted output. Thank you.

File

https://docs.google.com/spreadsheets/d/1llzKd19RqessSVGvpFFiacO2T4To3dDR/edit?usp=sharing&ouid=10425...

PBIX

https://drive.google.com/file/d/1KGolMGo50e01LJy3cCUa6_TfabjGUCZ0/view?usp=sharing