Dynamic Matrix / Bookmarks

Interesting challenge
I have a matrix with 4 measures. It is really slow. What I want to do is to allow the users to chooose which of the measures is shown.
What the solution must do is to allow the user to select 1 to all of the 4 measures. The Matrix shown will then reflect the selected measures.

  1. I have tried to use a slicer to allow the users to select the measure(s). Then I used the slicer to short - circuit the calculation. This did not improve the performance.

  2. I tried https://dax.tips/2020/01/28/dynamic-columns-based-on-values/
    This is an awesome way to create a dynamic table in DAX. However not really suitable for our users as they want to personalise the visuals.

  3. This leaves me with Bookmarks. With 4 measures that could mean 16 bookmarks. Thats ok. However 16 buttons does not look good.

Is there a way to use a measure for the bookmark to be activated? That way I can work out which measures are required from the slicer. Use a measure to create the bookmark name dynamically.



That is an interesting problem. The idea of maintaining and routing through 16 bookmarks makes me queasy.

Have you considered doing this via page navigation buttons to hidden pages? What I’m thinking about is something akin to the “pseudo slicer” approach I discussed here:

What you could do is something of a nested version of this, where the first level button selection is “How many measures do you want to include?” This would then bring you to an intermediate page with another set of buttons representing the different measure combinations, based on your first choice. Then, selection at the second level would route you to a specific personalizable page, with the matrix based on the measures selected.

  • Brian

Question - are you using the “Personalize Visuals” feature available in PowerBi, where the user can actually choose what they are seeing on a certain visual? (as below)

Or, by personalize, are you meaning the slicer to select measure option?

if it’s the latter - I suggest investigating the new Personalize Visuals feature. You can set only certain visuals (such as one table in the report) to be personalized. This feature has just moved out of preview into GA.

1 Like

The users are using the new personalise visual option.

The main issue is when they open the page it takes forever so I am trying to reduce the number of measures so it responds in a timely manner.


Seems that you may need to do some optimizing of your report

First, take a look at how many visuals are on the page that is taking forever to load. This means EVERYTHING, how many cards, how many buttons, how many tables (visible and not).

Next, look at your DAX - do you have any measures thart are extremely slow?

The reason I ask, is that I have what I call a ‘playground’ report for my management team to use, and everything in that report can be personalized (except for a couple of text fields where I’m providing tips on why adding certain measures to the visual may not be a good idea). And all the pages render super fast, but I have only 2-3 visuals on each page.

I really dont want this to add pages. As it goes into an App this would bloat the App so Navigation could become impossible.

The closest I can come is a slicer. From that I can understand the measures selected.

Then a button to say ‘Apply’, I was hoping this button would then be able to go to a bookmark based on a measure. Struggling with that.

I looked at visability for buttons but cant seem to make them visible / invisible based on a measure.

I looked at Slicer on the Measure and that could have worked but would bloat an already bloated data set.


Thanks. Been down the road of looking at the data model. Currently I have managed to only supply 50% of what they want so not a good start.
Based on that the Performance Analyxer is howing 10,000 ms to render one visual (Matrix) and 8000ms in total for four charts.

This is the first page so needs to give an overview and an impact. Current impact is to sit back and wait.

This attempt is to reduce the measures in the Matrix which from testing will allow the Matrix to render quicker.

Running out of options again to speed this up.

I recall seeing a trick about this recently, where you can change the transparency value of an item based on a measure. Let me dig around and try to find where I saw that…

UPDATE: here it is:

  • Brian

You can always “colour” something transparent … I keep this measure in all my models just in case:

Transparent_Color = "#FFFFFF00"

Hope it helps.


The way to make a button visible or not based on a measure is through conditional formatting - so far, that is the only way I have seen to make any color transparent.

The trick is, that you will need measures for each of the button states - so if your button is normally formatted to look different on press or hover, you’ll need to have measures to cover that as well.

And if your model is already struggling, I don’t know if adding measures for each state of the button, and for each feature (outline, text, fill, and possibly icon) is it worth it?

I was trying to build a quick mock up for you of how this would work, and got quickly frustrated by the number of measures just to demonstrate this:

The alternative, is to add the measure to the color of the text, so something like:

Masking Button Text =
VAR Color = “B3B0AD”
VAR Transparent = “FFFFFF00”

IF( [Sum Total] < 15, Color, Transparent )

The addition of “00” at the end of the color code makes it transparent, and I just use the FFFFFF code (white) for convenience.
Note that this method requires a different measure for each color used in the button.

The color measure is applied in the Visualization options - click the fx button:

1 Like

Would adding a measure that is for button visability add to the model in a substantial way? In my head I would have thought that it would be minimal impact.

So it looks like we can do this but we are adding a lot of maintenance as the report gets developed over time.

Many thanks will look at implementing this .


Chris Webb had an interesting post on this issue a few weeks ago:

The performance hit may come primarily from the visuals themselves, even if they display no data, rather than the measure(s) that controls the transparency.

  • Brian

will go through that again. I think I read that recently. Looking through Performance Analyzer the visual content is low.
So on my Matrix the
DAX Query is 8,121 ms
Visual display is 125 ms
Other is 3,339 ms

Total is 11,585
Total for the Page is circa 29,000 ms

This is a little distorted as I am sure there is some data flows refreshing. But this is just one user and I am sure its just going to go down hill.

Hi @ells,

I am not sure if it will work for you, but I created a sample report, where the user can decide using a slicer whether the measure will perform the Dax calculation or simply return blank. See attached:

Using slicer to control measure population.pbix (825.1 KB)

The report has a helper table which contains the measures you wish to control and which the user will select from. It then also has columns for each of those measures, so using a SUM you can determine if each one is selected or not (there may be easier ways of doing this?)


I then used the following DAX for each measure. This essentially checks that a measure has been selected (required as a default slicer doesn’t seem to offer the option of Force Selection) and then checks that the measure in question has been selected. If it has then run the DAX of your time consuming measure.

Order Qty = 
// Original DAX measure = SUM(Sales[OrderQty])
var __IsFiltered = ISFILTERED('Measure Options'[Measure Name])
var __IsSelected = SUM('Measure Options'[OrderQty])
    IF(__IsSelected <> 0, 

Would this work for you?



Thanks. Been through that option. It has to surface the data quicker and adding a slicer comes with a cost, and what you gain by short circuiting the calculation you lose by surfacing the slicer.

The feedback to the users has been “this is as good as it gets”

This will start an uncomfortable discussion but looking at the spec for the next report that one will make this one look really fast.

Not enjoying this.