• Samuel Woldu

Show/Hide Measures based on User Selection

First of all if you’re lucky enough to have access to Calculation Groups, then you should use it instead. It provides you with all the features I’m about to cover with the least amount of effort, assuming you don’t then keep on reading below. Corresponding PBIx can be found here and PBI report link here.

Developing reports can be a challenging task, especially when you need to implement a feature that isn’t a button you can click and activate using the Power BI Desktop interface.

An example of such a feature is the ability to show/hide measures based on user selection for any given visual.

This is a common request, given that a business could use 30+ measures to evaluate how good or bad business is performing. In most cases the business doesn’t want to or need to see all the measures at the same time, but they need the flexibility to be able to at any given time.

By using a combination of DAX and a disconnected table, this feature can be implemented.

Benefits of taking this approach:

- improved visual rendering as measures are not being evaluated until they are visible (I advise you test this for your specific scenario)

- improved flexibility for end users

- improved report readability for end users

Considerations before taking this approach:

- Loss of the ability to have grand totals for the rows

- Increased DAX development work to get the rolling totals correct (not covered in this blog).

- If the measures contain different data types, you will have to manually assign the format for each data type, except for the applied data type format.

- As a result of the latter the business lose the ability to sort by the preferred measure (which is more of a Power BI Desktop problem) as the FORMAT function converts numbers to text.

In this example I have 10 measures that the business could use to review how things are going. In a real-life scenario, you could have anywhere between 5 and 50 measures. The report I’ve created is to highlight the functionality of the workaround and not the best way to visualise data.

Step 1)

Create a disconnected table that has 2 columns. The “Metric” column will be the values available for end users to select, so make sure they are easy to understand. They don’t necessarily have to match the measure name, but the closer you get the measure name, the easier things are to manage.

Step 2)

Next you need to create a measure which uses a combination of the following DAX functions:

SWITCH and TRUE which will evaluate to true dependant on the combination of filters selected.

SELECTEDVALUE checks to see if the metric selected by the end user matches the column/row filter context. When it evaluates to TRUE it will return the corresponding measure.

FORMAT used to format your measures to the required data type

Step 3)

Place the “Metric” column on the page in the form of a filter to allow for end user selection.

And now you’re done! Given the way that filter context works with DAX you will find that that you'll be able to slice and dice the measure by any of your dimension tables. You need to place your dimension column above the measure and then expand the hierarchy for it to work.

There are 2 alternative options you should consider, these are:

Option 1: Bookmarks & Buttons (B&B)

If the business always groups certain measures together (e.g. Sales £, Sales Units, Refunds £, Refund Units, Discounts etc.) then combining B&B allows you to show/hide visuals which only contain the measures for the specific group.

If this is not the case and the business changes the way they group measures together sporadically, dependent on what the boss wants them to investigate, time of year or individual data exploration approach you would eventually start to find B&B being a headache to maintain. Reason being you would need to create and maintain a B&B for every possible combination. This increases report development time and would likely result in a negative end user experience.

Option 2: Self Service BI

With the recently released Build Permission feature, developers can focus on the data model and measures. They can then allow the business to do the visualisation part of the report, using both Power BI Desktop and Service. A great video explaining this feature has been done by the Guy In a Cube duo here.

If the business is competent enough to visualise their own reports, this approach would be my go-to option.

Let me know if this is something you see yourself using or if would use a different approach.


102 views0 comments