#DAX #PowerBI #RLS #PowerQuery #M
Sometimes, we have to deal with incomplete and messy data; of course, following Roche's Maxim of Data Transformation would mean that we will wait until all the systems involved fix the mess, but sometimes, we can't wait. If you want to know more about this maxim, read this great article: [Roche’s Maxim of Data Transformation – BI Polar (ssbipolar.com)](https://ssbipolar.com/2021/05/31/roches-maxim/)
# Setting the stage
Here, I will describe the use case, some of my challenges, the current data architecture, how the source data looks, and what I want to achieve.
## The use case
The business case that made me approach Row Level Security (RLS) a little differently is more complex and would require a book instead of an article, even if somewhat lengthy. Here, I will focus on implementing RLS to a dimension table that represents a ragged hierarchy.
We often encounter this dimension type when looking at an organizational structure, such as companies/departments, team leads/team members, or a chart of accounts. What sets this type of dimension apart from regular dimensions is that root members do not have the same number of ancestors.
One of the many goals is to provide access to users and all the items forming the hierarchy from the current point of view, the user's current position in the hierarchy down to all the root members.
This can be solved by following the pattern Reza Rad has described in this article: [Dynamic Row Level Security with Organizational Hierarchy Power BI - RADACAD](https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi)
For this solution, I was looking for a different approach because of two reasons:
+ the number of "items" inside my structure and
+ using DAX PATH functions requires only a single parent of a specific item; in my real world, this is not the case, and not because of messy data.
## The challenge
The dimension table is somewhat larger and for this reason, the overall goal was the possibility of using dynamic RLS with the most simple DAX statement. Unfortunately, the source data is messy.
This is how the source data looks like:
![[DAX - Unusual DAX, recursive M, and RLS - the data.png]]
From this data I want to create this:
![[DAX - Unusual DAX, recursive M, and RLS - the structure.png]]
The problems that I'm facing are manyfold. In this article, I focus on the following issues:
+ The manager of a person is only identified by the name
+ There are managers (identifiable by level L[x]) that have the same name, e.g. Kevin
+ The structure is incomplete for the structure S1.2
+ There is no direct manager for the person called hero3
I tackle the above issues using DAX :-)
I use a Power Query query to create a string representing the organizational structure. I do this by using a recursive custom function.
## The current architecture
My source data is a table in a Power BI semantic model (I can not access the model's source data directly), but I'm allowed to access the model's table from inside Power Query.
# The solution
I use the pbix file
[theSource.pbix](https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EddD5SwLpudEq0TCx6EcWv4BLqxKXjZS6xkFSNvusRlakA?e=TChQGp)
to create a semantic model in a workspace, this semantic model represents the source semantic model.
I use the pbix file
[theSolution.pbix](https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ETEPqqJ0S61MrTZXuxUJT7YB7yTflLjFH1hEuyS6rdwfCQ?e=nePtr4)
to ingest the data of the table "theSourceData" from the source model into a second model - my model.
I do this by leveraging the XMLA endpoint of the semantic model (if you want to try this at home, you need at least a Premium Per User backed workspace). I leverage the "SQL Server Analysis Services database" connector when querying the source semantic model. While querying the source table, I add a column (using ADDCOLUMNS) that returns the manager's email address for the current "name." This is the first step to enable a "simple" DAX statement when implementing RLS.
The pbix file "theSolution" contains the custom Power Query function that creates the string I use for configuring Row Level Security. The first step is to connect to the source semantic model; for this, I use a custom DAX query to add a column to the source table while querying the data. When sourcing data from a "remote" model for imort, it's a good idea to use a custom DAX query instead of navigating to the appropriate table. The reason custom DAX is way much faster is because the navigation approach is sending MDX, and this, of course, takes a while :-)
## Connecting to the XMLA endpoint
From all the connectors availablel I choose SQL Server Analysis Services Database:
![[DAX - Unusual DAX, recursive M, and RLS - the data connection.png]]
You will find the complete code of the DAX query here: [theDaxQuery.dax](https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EW-Milw_4ytLl7o_CTLdDqcBpnW5QlLqhmpCrthAFurQDw?e=EOWf0Z)
and of course, in the pbix "theSolution.pbix."
We must consider one thing when importing data from a semantic model using the above approach.
The column names are returned like so:
'tablename'[columnname]
![[DAX - Unusual DAX, recursive M, and RLS - the technical names.png]]
Transforming the column names into readable column names can be achieved using the following M code, assuming the step of the connection is called "Source."
```
#"RenamedColumns" = Table.RenameColumns(Source,List.Zip({Table.ColumnNames(Source), List.Transform(Table.ColumnNames(Source), each Text.BetweenDelimiters(_, "[", "]"))}))
```
After this little "hack" the table looks like this:
![[DAX - Unusual DAX, recursive M, and RLS - tidy names.png]]
## Some (unusual) DAX
When looking at the source data, we will realize that it will become difficult to determine the "Manager's email for hero1 and hero2 as well as for hero3. It's difficult for hero1 and hero2 because there are two rows with the name Kevin. It's difficult for hero3 because there is no value for the field themanager at all.
To tackle this challenge it's necessary to filter for the values of the column theStructure. I'm pretty sure that this can be done using a pretty complicated combination of string related DAX functions but I always do this by creating a table that contains the structure. The possible structure for hero1 and hero2 looks like this:
![[DAX - Unusual DAX, recursive M, and RLS - string to table.png]]
I use the DAX code below to create the intermediate table theHierarchy:
```
SELECTCOLUMNS(
ADDCOLUMNS(
FILTER(
GENERATE(
GENERATESERIES( 1, LEN( currentStructure ), 1 )
, ROW( "pos", IF( MID( currentStructure, [Value], 1 ) = ".", 1, 0 ) )
)
, [pos] = 1
)
, "Structure", MID( currentStructure, 1, [Value] - 1 )
)
, "Structure", [Structure]
)
```
The unusual part is this:
```
GENERATE(
GENERATESERIES( 1, 50, 1 )
, ROW( "pos", IF( MID( currentStructure, [Value], 1 ) = ".", 1, 0 ) )
)
```
I use the DAX function GENERATESERIES to create a table of integers based on the length of the value of the field theStructure.
GENERATE is a table iterator that iterates across the first table and makes the rows available for the second table. I use the table constructor function ROW as the second table for GENERATE to flag the character (the position inside the string when it equals the separator "."
Both the functions ADDCOLUMNS and FILTER use the flagged position to filter for the marked position and extract the partial structure of the current value of theStructure in combination with the DAX function MID().
This allows the intermediate table theHierarchy to be used at later stages with the DAX function TREATAS.
## The recursive custom Power Query function
Querying the source semantic model in import mode allows the use of a Power Query custom function to implement recursion that adds the hierarchy as a string to each row. The magic of recursion functions is enabled by the recursion operator @
The below function is more or less a simple recursive function.
```
(Parent as any , Child as text, InitialPath as text, N as number, plist as list, clist as list) =>
let
parent = Parent,
child = Child,
positionofchild = List.PositionOf(clist, parent),
newparent = if positionofchild = -1 then "" else plist{positionofchild},
initialpath =
if parent = "" or parent = null
then
if child = "" then InitialPath
else
if parent <> null
then Text.Combine({parent , InitialPath},"|")
else InitialPath
else
Text.Combine({parent , InitialPath},"|"),
path =
if parent = "" or parent = null and N < 11
then
if child = "" then ""
else initialpath
else
@createHieararchyPath(newparent, parent, initialpath, N+1, plist, clist)
in
path
```
The custom function is called "createHieararchyPath", and this line starts the recursive iteration:
```
@createHieararchyPath(newparent, parent, initialpath, N+1
```
The below image shows the column "thePath", please excuse the bad readability, but this is a column that was never designed for readability 😎
![[DAX - Unusual DAX, recursive M, and RLS - the unreadable result.png]]Please be aware that recursion comes with some downsides:
+ it's mindboggling
+ recursion is not that fast inside Power Query.
## The "simple" DAX to implement Row Level Security
The below DAX statement is what I consider one of the simplest DAX statements possible using dynamic RLS on top of a ragged hierarchy 😎
```
Containsstring([thePath], "|" & userprincipalname())
```
This is how it looks like in the "theSolution.pbix"
![[DAX - Unusual DAX, recursive M, and RLS - RLS configuration.png]]
And this is what
[email protected] has access to:
![[DAX - Unusual DAX, recursive M, and RLS - result RLS.png]]
# Conclusion
Overall I consider this solution great because it allows me to use one of the most simple DAX statements to implement Row Level Security, basically the single goal of all the above. I love this solution because it combines DAX and M, and this time, it's the other way around: first, DAX, and then Power Query.
---
Thank you for reading