Table should show hierarchical data according to slicer value.

[SanketBhagwat]

Hello.

I have a table which contains hierarchical data as Groups->Divisions-> Functions.

I have added those 3 values in 3 different slicers.

What I want to show in the table is such that if I select the 'Group' slicer value, then the table should show only the corresponding divison value and not Groups and Functions.

Similar for 'Divisions', if i select on that slicer then it should show Functions in the table and not the other 2 column values.

If anyone knows any way to implement it, then please reply to this post.

I have also attached the screenshot of the data and the slicers.

[v-deddai1-msft · Accepted solution]

Hi @SanketBhagwat ,

The table visual will not show dynamic columns, but you can create a matrix visual.Please follow the steps below:

1. Create an index column in your original table,

2.Create a new hierarchy table for columns in matrix visual:

3. Create a new table for slicer:

Table 2 = UNION(SELECTCOLUMNS(Sheet,"value",Sheet[Groups],"child","Divisions","current","Groups"),SELECTCOLUMNS(Sheet,"value",Sheet[Divisions ],"child","Functions","current","Divisions"),SELECTCOLUMNS(Sheet,"value",Sheet[Functions ],"child","Functions","current","Function"))

4. Then create three slicer by the new value column from the table 2 and use the visual level filter to fix them to their hierarchy.

5. Create the matrix visual by the new measure:

Measure = SWITCH (
    MAX ( Table3[Column2] ),
    "Group", MAX ( Sheet[Groups] ),
    "Divisions",
        IF (
            MAX ( 'Table 2'[current] ) = "Groups",
            CALCULATE (
                MAX ( Sheet[Divisions ] ),
                TREATAS ( VALUES ( 'Table 2'[value] ), Sheet[Groups] )
            ),
            MAX ( Sheet[Divisions ] )
        ),
    "Functions",
        SWITCH (
            MAX ( 'Table 2'[current] ),
            "Divisions",
                CALCULATE (
                    MAX ( Sheet[Functions ] ),
                    TREATAS ( VALUES ( 'Table 2'[value] ), Sheet[Divisions ] )
                ),
            "Function",
                CALCULATE (
                    MAX ( Sheet[Functions ] ),
                    TREATAS ( VALUES ( 'Table 2'[value] ), Sheet[Functions ] )
                )
        ,MAX(Sheet[Functions ]))
)

Please refer to the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[SanketBhagwat]

I tried the same steps in my file.

But my matrix didn't exactly gave the output which you got in your file.

Although I implemented the same steps.

My matrix sliced correct data but it displayed the other 2 column values along with it too.

I have attached screenshot of it.

[v-deddai1-msft]

Hi @SanketBhagwat ,

You need to create bi-direction relationship between table 2 and table3, by child column and new column in table3.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Greg_Deckler]

@SanketBhagwat No way that I know of to essentially have dynamic columns in the table. You could potentially create measures and use HASONEVALUE or ISFILTERED to determine what is or is not filtered. See this dynamic title measure example for how you can use these functions. You would return BLANK in the event that you don't want to show values in certain situations. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Most-Amazing-Mind-Blowing-Dynamic-Slicer...

[SanketBhagwat]

Here is the link to the dataset;

https://nicesoftwaresolutions1-my.sharepoint.com/:x:/g/personal/sbhagwat_nicesoftwaresolutions_com/E...