Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

jo_brough

Dynamic Hideable Columns in Power BI Table Visual

 

A full demo of this trick, including all the set-ups seen in screenshots, is attached as a .pbix file.

 

Tables in Power BI can be a very dense way to present a lot of information on one screen, which, while efficient, will typically lead to the viewer needing to filter out a lot of irrelevant information to see what they need. This specific situation of needing to show only the figures of interest leads to the introduciton of Power BI's wonderful slicer visual, which allow users to rapidly and intuitively hide rows of a table, with the remaining rows naturally shifting upwards to fill in the gaps.

 

By default, slicers offer a way of filtering rows, but there is no intended method of hiding columns using the built-in Table visual. However, that doesn’t mean it can’t be done! The effect can be re-created using entirely built-in features of Power BI, linked by a few tricks: a slicer tied to a lookup table of all the different column names, and a layer of measures that perform some logic for each column displayed in the table.

The table in its natural form....The table in its natural form....

Consider the above scenario, in which data has been collected for a number of different categories of cat. The table has seven data columns, although there could easily be more, and even this many would become unwieldy with longer headings. If a reader were only interested in some of the columns – e.g. data columns 1, 2, 6 and 7 – then there would be a great deal of unnecessary data for their eyes to sift through. What we want is the ability to hide columns freely, and with no gaps: after all, when we use a slicer to remove rows, the remaining rows naturally shift upwards to fill in the space.

And now with columns 3, 4 and 5 removed.And now with columns 3, 4 and 5 removed.

Here, I will need to establish some terminology: I will be referring to the columns of the Power BI table visual as the display columns, and the columns of the underlying data table as the data columns. In a typical table set-up, the two concepts would be inseparable: the fourth column of raw data would always appear in the fourth position. The key to our success here will be abstracting the two apart from one another, in order to allow for the display columns to dynamically change which data columns they, well, display! The layer of "abstraction" lies within a set of measures: we will be creating one for each display column, each of which will be able to pay attention to a slicer.

 

How exactly does that slicer work, though? After all, they don’t normally allow for listing column names by default. The slicer in this solution is actually tied to another table, - a hidden list of the names of each data column, along with ordered ID numbers, which I’ve elected to name ‘Table Headers’. When the slicer is used, values will be filtered out of this small table, and measures in the main table visual will be programmed to react to this, and determine what data is displayed where.

The hidden 'Table Headers' table represents each item in the slicer...The hidden 'Table Headers' table represents each item in the slicer......Which can be sliced away....Which can be sliced away.

Each display column is occupied by a unique measure, in my case simply named [Display Column 1], [Display Column 2], ..., [Display Column 7]. The unfortunate repetition of code across these is the least elegant part of this solution, but luckily the redundancy can be minimised to just a few lines with a smart parameterisation – this technique is detailed at https://www.sqlbi.com/articles/parameters-in-dax-measures/. Now, the interesting DAX code can be consolidated to a single [Display Column Generic] measure - while each of the Display Column measures simply calls the generic version and passes its column number as a parameter.

 

Display Column 2 =
VAR colnum = 2

RETURN
    CALCULATE (
        [Display Column Generic],
        'Table Header Parameter'[Parameter] = colnum
    )
-- pass colnum as a parameter by using CALCULATE and the parameter table, 'Table Header Parameter'
-- the parameter table is simply a table of every possible value of colnum - i.e. the numbers 1 through 7

From here, the generic measure is as follows:

Display Column Generic = 
-- display column number passed as parameter
VAR display_col =
    MAX ( 'Table Header Parameter'[Parameter] )

-- if i am the nth display column, then
-- data column number = the nth smallest data column that hasn't been sliced out
VAR data_col =
    MAXX (
        TOPN (
            display_col,
            VALUES ( 'Table Headers'[ID] ),
            CALCULATE ( SUM ( 'Table Headers'[ID] ) ), ASC
        ),
        CALCULATE ( SUM ( 'Table Headers'[ID] ) )
    )

RETURN
    IF (
        display_col > COUNTROWS ( 'Table Headers' ),
        BLANK (),

        -- display data from the relevant data column
        SWITCH (
            data_col,
            1, MAX ( 'Union Table'[DataCol1] ),
            2, MAX ( 'Union Table'[DataCol2] ),
            3, MAX ( 'Union Table'[DataCol3] ),
            4, MAX ( 'Union Table'[DataCol4] ),
            5, MAX ( 'Union Table'[DataCol5] ),
            6, MAX ( 'Union Table'[DataCol6] ),
            7, MAX ( 'Union Table'[DataCol7] ),
            BLANK ()
        )
    )

This measure works in three steps:

First, it reads in the display column number from the parameter table.

Secondly, it uses a mathematical trick to determine which data column should be displayed. Given that this is the nth display column, it should display the values of the nth non-hidden data column. Thus, it uses the TOPN function to calculate the nth smallest ID number in the 'Table Headers' table.

Finally, a SWITCH statement is used to map each possible data column number to the actual contents of the data column.

 

All that remains (well, for now) is to place our Display Column measures in the table and tie them to a slicer, and, ta-da!

Set the Display Col measures in the table...Set the Display Col measures in the table......and watch them work their magic....and watch them work their magic.

Something's not right, is it? Yep, these tables are missing their headers - and this is a whole new challenge to solve. In the examples at the top of this article, note how the column headers dynamically shift with the moving columns. This isn't covered by our solution so far, which feebly displays static names at the head of its columns. Luckily, I have two alternate tricks to deal with it!

 

Header solution 1: An extra row in the table. (Not recommended)

My first attempt at solving this problem was perhaps more convoluted than necessary. I used the UNION function to create a new table which was identical to the old table (or, at least, contained all the columns I wanted to display), but featured an additional row, bearing the names of each of the columns. In this situation, I also had to write an additional calculated column which I used to sort Categories and ensure this header row rose to the top.

Once the header row is in place, conditional formatting could be used to highlight it, and the text of the defunct headers can be set to blend in with the background.Once the header row is in place, conditional formatting could be used to highlight it, and the text of the defunct headers can be set to blend in with the background.

Header solution 2: Use a matrix overlay.

This is my recommended solution, and uses the cheapest of all tricks: cover up the offending table headers with a different visual!

 

The items in the matrix...The items in the matrix......will automatically react to the slicer....will automatically react to the slicer.

After some experimentation, I realised that it was very simple to display the contents of the 'Table Headers' table horizontally in a Power BI matrix visual - the top row of the matrix would behave exactly like the headers of a table visual, and thus could be styled and simply dropped on top to cover it up. This did require spending a few minutes fixing up the styles and lining up edges, but involved no messing with data. In both of the above screenshots, a copy of the matrix has been placed above the table to demonstrate what it looks like by itself.

 

And with that, we're done! I've already used this technique in a report, and expect to use it again. With relatively simple code and a short list of tricks, we have introduced a behaviour to Power BI that is not intended but seems to fit in with the software like it was always there. I have no doubt there are other ways of achieving this effect using other techniques, and it could very realistically be created in a custom visual, but this is achievable with features built into Power BI by default.

 

Jo Brough

Power BI user since 2019

Comments
Anonymous

Good idea, but if you hide the column headers, how to sort data by column header?

Excellent technqiue to hide and display columns dynamically! do you know if this tecnique is working for line chart? for example, each line is from a column and user can select which line(s) are shown in line chart by selecting columns in a slicer. I tried converting the table to line chart directly in your demo pbix file but it didn't work. Thanks!

This technique is certainly better than no workaround.   Thanks for posting.  My only question - suppose your measures have different display formats (some as currency, some as whole numbers or percentages, etc.)

I'm not aware of a way to set dynamic formatting.  If there is a way, that's great.  If not, that detracts from this being used as a solution for something presentation-quality.

 

thanks...Kevin

 

very nice!  however, the column headers are still there.

I have a client that is asking, very similar to  your example, how to hide column(s), the data and the column header.  The table has about 60 columns, and it makes it difficult to keep scrolling left and right.

do you think there is a way to do that?

 

Anonymous

Great job, super useful!

One question, how did you create the table "Union Table"?

Many thanks