Dynamic Columns based on values

I came across an interesting request last week from someone who wanted to dynamically control columns shown in a Power BI table visual – based on the values within each table.

There are lots of blogs showing different techniques on dynamic columns, but the requirement here was to base the decision on whether to show or hide the column, using logic that prefers columns populated with the most cells.

The report needs to show a set of KPI data in a tabular form (table or matrix visual). Each column represents a different KPI and each KPI will have its own calculated measure. There are approximately 50 KPI’s in the model, and the objective is to find a way to remove the need to scroll horizontally to find the columns that have the most data.

The number of columns shown needs to be interactive with the end-user having the ability to show more (or all) of the columns if they wanted.

This challenge can get solved in several ways, but I thought I would see what a DAX-based solution would look.

The working PBIX file for this blog can be downloaded here.

The starting point is a table visual in the report page called “The Problem”, that contains 11 columns of measures.

Each column is based on a different measure, and will return a value depending on the product from the left-hand column.

Imagine there are 50 of these columns and depending on the filter settings, some columns may return lots of values, while other columns may only display a few (or even none).

In my example, I created 11 measures that return a mixture of values. The contents of these measures could get replaced with your logic.

The DAX-based solution gets shown in the report tab named “The Solution”.

The report shown in the video has a matrix of columns. Each column represents a specific KPI and is based on a measure. The slicer just above the matrix allows a user to select a range of numbers that control which columns get shown. If the chosen range is from 1 to 4, then the first four columns with the most number of rows will be displayed.

If a user sets the slicer to use the entire range, then all columns will be displayed, The user can also set the slicer to show the least populated columns buy setting a range starting near the end.

The mechanics

To get this working, first I created a disconnected table with 1 row per measure. I have 11 measures, so my disconnected table has 11 rows. I used the following code that created three columns.

Dynamic Measures = 

    "Measure ID" , INTEGER ,
    "Measure Name" , STRING ,
    "Format" , STRING ,
            { 1,"Revenue","CURRENCY"} ,
            { 2,"Measure A","#,###"} ,
            { 3,"Measure B","#,###"} ,
            { 4,"Measure C","#,###"} ,
            { 5,"Measure D","#,###"} ,
            { 6,"Measure E","#,###"} ,
            { 7,"Measure F","CURRENCY"} ,
            { 8,"Measure G","CURRENCY"} ,
            { 9,"Measure H","CURRENCY"} ,
            {10,"Measure I","CURRENCY"} ,
            {11,"Measure J","CURRENCY"} 
Table of measures for dynamic column selection

With this table in place, the [Measure Name] column from this table can get used on the matrix visual. This configuration provides the structure for showing/hiding columns dynamically.

The next step is to create a calculated measure to be used in the matrix. This measure will make calls to other measures to produce the values shown in the matrix.

Dynamic Measure = 
VAR myMeasureName = MIN('Dynamic Measures'[Measure Name])
VAR myProduct = MIN('Product'[Product])

VAR myWorkingTable = 
                ALLSELECTED('Product'[Product],'Product'[Segment]) ,
                ALL('Dynamic Measures'[Measure Name],'Dynamic Measures'[Format])
            ) ,
        "Measure Value" ,
            [Measure Name],
            "Measure A" , [Measure A] ,
            "Measure B" , [Measure B] ,
            "Measure C" , [Measure C] ,
            "Measure D" , [Measure D] ,
            "Measure E" , [Measure E] ,
            "Measure F" , [Measure F] ,
            "Measure G" , [Measure G] ,
            "Measure H" , [Measure H] ,
            "Measure I" , [Measure I] ,
            "Measure J" , [Measure J] ,
        ),NOT ISBLANK([Measure Value]))

VAR myMeasureTableToRank =

	[measure name],
	"Count of Non Blank Values",
	VAR t = [Measure Name] RETURN COUNTROWS(filter(myWorkingTable,[Measure Name] = t)))

VAR myMeasureTableRanked = 
        myMeasureTableToRank ,
        "Rank" , RANKX(myMeasureTableToRank,[Count of Non Blank Values],,,Dense)

VAR myCellValue =
    MAXX(FILTER(myWorkingTable,[Measure Name] = MyMeasureName && [Product] =myProduct),[Measure Value])

VAR myCellFormat = 
    MAXX(FILTER(myWorkingTable,[Measure Name] = MyMeasureName && [Product] =myProduct),[format])

VAR myRank = 
    MAXX(FILTER(myMeasureTableRanked,[Measure Name] = myMeasureName),[Rank])    

VAR myFinalValue =
        NOT ISBLANK(myCellValue),

         myRank IN FILTERS('Column Filter'[Value]) ,

The logic of the measure does the following:

  • Creates a working at line 5 that computes all values for all measures. This table gets assigned to the variable called myWorkingTable. You need to manually add a line to the SWITCH statement for every measure considered. You need to ensure this is in sync with the rows in the physical table called ‘Dynamic Measures’
  • Creates a new table that counts the number of non-blank rows per column. This result is grouped and stored in a variable called myMeasureTableToRank
  • Adds a ranking column to the table stored in the myMeasureTableToRank variable. This new table is stored in the variable called myMeasureTableRanked
  • The myCellValue variable is assigned the appropriate value for the current cell from the myWorkingTable table.
  • The myCellFormat variable is assigned the appropriate format string for the value. This is pretty cool!
  • The myRank variable is assigned the appropriate rank value for the column
  • The rest of the measure decides if the myRank value sits within the current range specified by the slicer. If it does, the value is shown otherwise the measure will return a blank.

This measure gets added to our matrix as the only measure. The measure will only return a value if the ranking of the column happens to sit within the range determined by the slicer.

The default behavior of a matrix is if column is full of blanks it will not be shown. This can be overridden in the formatting properties – but we don’t want that in this case.

I set the alignment formatting for the matrix to be right aligned. Otherwise the code allows for values to be shown using a variety of formatting strings by populating the measure table appropriately.

The last element was to add a slicer to the report that allows users to set a range of values to control which columns are shown.

An ordering column can be added to the ‘Dynamic Measures’ table if column order is important.


This solution seemed to work pretty well and could be useful for specific use cases. It was pretty straight forward to implement and included some neat tricks such as :

  • Pre-calculating a table of values in a measure
  • Adding a ranking column on the fly to a virtual table
  • A nice way to add a format string on the fly for different types of output

This small example performs pretty well – but care would need to be taken if the logic in the measures becomes complex. DAX would need to be super-optimised when working with larger data-sets, more columns and more complex logic.

Different rules can be applied to decide the prioritisation of column order – but the structure of the approach should pretty much stay the same.

Let me know if you use this. I’m always interested to hear when ideas shown in my blogs are applied,

4.9 13 votes
Article Rating

Leave a Reply

Inline Feedbacks
View all comments
3 years ago

awesome solution Phil

Nikola Ilic
3 years ago

Brilliant solution! I needed to read the article twice, in order to understand what is happening in the background:)

Josef Prakljacic
Josef Prakljacic
3 years ago

Hello Phil,

it’s time to thank you for your great work and for the time you put into sharing your knowledge – thank you very much!

Thank you very much for the description of your solution regarding dynamic attributes/columns.

But I would have an additional challenge in this regard 🙂

My customer would not only like to click the columns together but also change the column order in the Matrix Visual.

Would you come up with a solution here?

Thanks again for your work.


[…] Dynamic Columns based on values […]

3 years ago

Awesome Phil.

3 years ago

Hi Phil,

I have used your DAX for the dynamic column selection functionality.The report has been slowed because of the DAX query.Is there any way to reduce the DAX query so our report can be faster.

Please help us on this.


2 years ago

Hi Phil, Thank you for this post, first of all I must say these posts remind me that I barely scratched the surface of DAX knowledge, even though everytime I code something a little bit more complex, I keep thinking that I know a lot haha… And that’s what I want to ask. I can’t imagine ever being able to code something like this complex, I obviously understand the scope and all of these functions but still can’t imagine how one can be able to do something like this?! What’s the learning proces, I mean how can I get to… Read more »

2 years ago

looks nice, but do you think if this can also include dynamic dimension change ?

2 years ago

awesome solution Phil

But i have a small issue one column in my solution only have Test like on your table (Sales [Country])
tell me if is possible to do the same with this column with text?

Sandhya Soman
Sandhya Soman
2 years ago

Hi Phil,

I have almost similar requirement where I need to show columns from two different tables in a Calculated DAX table along with the column with measure name and measure values with the switch conditions same as you demo ed. I followed
the step you mentioned for populating VAR myWorkingTable, however it will not help me as I need to show columns from two tables. Could you please help me on this

2 years ago

Performance seems like a significant concern. How would this mesh with Fusion? Is the engine smart enough to see through the SWITCH and recognize the same few measures are being called for the product column.

Cekou C.
2 years ago

Hi Phil,

Thank you for this tutorial.

I noticed that sometimes in the [Dynamic Measure] measure, you don’t reference the table name before the column [Measure Name].

“Measure Value”,
[Measure Name]

(Here, I would have expected ‘Dynamic Measures'[Measure Name])

Do you do that for a specific reason? Would the behaviour of measure change if I put somewhere ‘Dynamic Measures'[Measure Name] instead of [Measure Name]?

Thank you.

Cekou C.

2 years ago

This is EXACTLY what I was looking for thank you! Have you experimented at all with adding conditional formatting options too? Example – if the measure name is YOY Sales % and the value is X then red, if the value is Y then Green. If the measure name is YOY Margin % and the value is A then Green if the value is B then Yellow? I think it could probably be done in the measure but I am having a problem figuring out the syntax. Any help you can provide would be amazing. I’m also working on adding… Read more »

Daran Davis
Daran Davis
2 years ago

Very nice Phil !
I also encountered a need to have the columns of the table in a specific order ie non-alphabetic.
For example in your case having Revenue appear as the first column which is alpbetically greater than Measure.
Any suggestions for expanding this solution with that consideration?

2 years ago

Phil, Awesome code first of all. Second what if say you have two tables of products? Products1 and Products2, and you wanted to pull measures from each one into your dynamic table?
“Measure A”, ‘SlicerTable'[Measure from first table]
“Measure B”, ‘SlicerTable'[Measure from second table]

When I tried it im getting blank data from the second table even though if i put ‘SlicerTable'[Measure from second table] directly into a Card Visual it shows correctly

2 years ago

Hi Phil,

Thank you for this solution. Hopefully I haven’t misread this, however your problem statement starts out with a Table visual and your solution is based on a Matrix visual. For me, this is problematic as conditional formatting in matrices is more limited than in Table visuals. I am forced to use a Table Visual in this case but would still like to be able to establish dynamic columns.

Do you have a variant of this solution for Table visuals as well?

Jaymin Patel
Jaymin Patel
2 years ago

Hi Phil,

I have used your solution and it was really userfull, but I am facing column ranking issue, so could you please help me in that.

If I want to make some column name dynamically then can I do it?

Thanks in advance!!!
Jaymin Patel

oded dror
1 year ago


Is there any way you can add another slicer that change also the column from Products to Country Dynamically?
Without having to create a buttons or bookmarks

2 months ago

Hi Phil,
BIG THANK YOU for this solution!! This is what I really needed, although I had to read it twice and then try on my own pbix file to understand what’s going on there – it looks great and saved me so much time, s THANK you once more, have a nice day, v.