How to capture a folder hierarchy in a slicer

[tfanaswala007]

Hi Power BI enthusiasts!

I have data that contains a sample folder structure that looks like this:

Root

• Documents
  • Work
    • Reports
    • Projects
  • Personal
• Photos
  • Vacation
  • Family

I have stored the data in a table like this:

folder	parent
Root
Documents	Root
Photos	Root
Work	Documents
Personal	Documents
Vacation	Photos
Family	Photos
Reports	Work
Projects	Work

More folders and sub-folders can be added later on, so this hierarchy is not static, which means the depth of the hierarchy can increase. Is there a way in Power BI to configure the data model so that I can create a slicer that looks like the bulleted version of the current folder hierarchy?

Appreciate your help!

Taha

[OwenAuger · Accepted solution]

Hi @tfanaswala007 

This is an interesting one 🙂

I have attached a sample PBIX.

The steps I would follow to set this up are:

1. In Power Query, flatten the folder hierarchy by adding "Level" columns using this function.
2. In Power Query, create a field parameter table which contains one row per "Level" column.
3. Configure the table from step 2 as a field parameter using Tabular Editor.
4. Place the field parameter on the Hierarchy Slicer visual and enable "Hide Empty Members".

Here is the detail:

1. In Power Query, flatten the folder hierarchy by adding "Level" columns using this function.

I set up a parent-child table similar to yours (with "Secret Projects" added under "Projects" for testing purposes), with an additional row containing "Root" with no parent, and an additional "Name" column.

Then I applied the function fnFlattenPCHierarchy (from here) with a slight modification to format Level numbers as two digits.

After tidying up and removing some columns we don't need, the resulting Folder is shown below. You could construct this table another way if you like.

2. In Power Query, create a field parameter table which contains one row per "Level" column.

I used this code to build this field parameter table (assuming that the first table is called Folder).

let
    Source = List.Select(Table.ColumnNames(Folder), each Text.StartsWith(_,"Level")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), type table[Level = text], null, ExtraValues.Error),
    #"Add Ordinal" = Table.AddRankColumn(#"Converted to Table", "Ordinal", {"Level", Order.Ascending}),
    #"Added Level Column" = Table.AddColumn(#"Add Ordinal", "Level Column", each "Folder[" & [Level] & "]", type text)
in
    #"Added Level Column"

The table looks like this:

3. Configure the table from step 2 as a field parameter using Tabular Editor.

To set this up to act as a field parameter, then make these changes in Tabular Editor (similar to this article😞

(a) For column Level Column, add a JSON item to Extended Properties as shown:

(b) For colum Level, set Group By Columns property to Level Column.

(c) Set Level and Level Column to Sort By Ordinal.

(d) Hide Level Column and Ordinal.

4. Place the field parameter on the Hierarchy Slicer visual and enable "Hide Empty Members".

(a) Add the Hierarchy Slicer custom visual from AppSource.

(b) Add the field 'Folder Field Parameter'[Level].

(c) Right-click the field and choose "Show values of selected field".

(d) Set "Hide Members" to Empty in the format pane.

End result in report:

The Folder Hierarchy will expand/contract on each refresh, as levels are added/removed.

Does something like this work for you?

Regards

[tfanaswala007]

What is the ParameterMetadata step doing? What do those key-value pairs signify?

[OwenAuger]

That step is required to "mark" that particular column so that Power BI recognises that it contains column/measure references and acts as a field parameter.

It is replicating what happens when a field parameter is created in the Power BI interface. 

The key/value pairs have no meaning in themselves (that I'm aware of). My guess is that further options might be created in the future to allow columns to perform other special functions.