Hello, I have implemented an Income Statement report based on the Financial Reporting tutorial and would like to be able to dynamically display multiple columns at the same time, selected by filter with “multiple selection” turned on, ie diplay both actuals and % of revenue side by side. I have a total of 5 options I would like to dynamically control: CY Actuals, PY Actuals, % Revenue, $ Change and % Change. This currently works if one item is selected, but if multiple are selected it is blank, which I think makes sense based on the SWITCH / TRUE logic.
This isn’t that hard to implement, but I find the setup for it not particularly intuitive. Here’s an excellent short video that explains exactly how to do it:
Hope this is helpful.
Thanks Brian, this is the functionality I want, but in my case, I am trying to dynamically change the values which are measures and not coming directly from a table.
That clearly increases the degree of difficulty on this, but I still definitely think it’s doable. Can you please post your PBIX file so we can play around with some different approaches?
Hi Brian, if you look at the Financial Details page of the Financial Reporting tutorial, this is essentially what I have. This sample file lets you choosed between “Actuals”, “vs Last Year”, and “% to Revenue” along with quarterly data, but only one selection at a time. I would like to view any of these simultaneously by changing that slicer to allow multiple selections.
OK, this was a fun one to work out - I think I finally have a solution that meets your requirements. Here are the steps:
harvest the results of the multi-select slicer using CONCATENATEX( )
Harvest Multi Slicer = CONCATENATEX( ALLSELECTED( 'Dynamic Columns'[Column Measure] ), 'Dynamic Columns'[Column Measure], ", " )
Using CONTAINSSTRING ( ), create dynamic versions of the measures you want to turn on and off where if they are not selected in the slicer they return BLANK( )
Total Sales LY Dynamic =
IF( CONTAINSSTRING( [Harvest Multi Slicer], "Total Sales LY" ) = TRUE(), [Total Sales LY], BLANK() )
Now if Power BI allowed you to hide blank columns the same way it does blank rows, we’d be done here. However, if you put these measures in the matrix and click the slicer on and off, the values will disappear but that header will still remain, which looks terrible. So we’ve got a few more steps…
Create a separate one column table containing your matrix column headers (could just be the names of the measures you want to include in your matrix)
Create a SWITCH( ) statement for the values in your matrix, using the header values created in 3) above:
Matrix Switch =
SWITCH ( SELECTEDVALUE ( 'Matrix Column Headers'[Headers] ), "Total Line Sales", [Total Sales Dynamic], "Total Sales LY", [Total Sales LY Dynamic], "Sales Rank", [Sales Rank Dynamic] )
Now populate your matrix visual as follows, using the elements created above:
- Click the down arrow next to Headers in the Columns well, and make sure the option “Show items with no data” is UNCHECKED.
And Presto! Now your matrix columns should be totally dynamic, based on the multi-select slicer.
I hope this is helpful. Full solution file posted below.
Works great, thanks for the solution!
Great solution Brian. Amazing work on this one
Thanks – I really appreciate that. As I said, this was a really interesting problem to work through start to finish.
For anyone using this solution, there are two subtle caveats that you’ll need to address in your specific implementation:
- In your disconnected table that contains the measure names used in the multi-select slicer, you cannot use the typical naming conventions that we use for measure branching, e.g., “Total Sales” and “Total Sales LY”. This is because of the way the CONTAINSSTRING function works. The following statement:
CONTAINSSTRING( [Harvest Multi Slicer], “Total Sales” )
will return TRUE for both Total Sales and Total Sales LY if the former is selected, whether the latter is actually selected in the slicer or not. This is why in the solution posted, while I have a measure called Total Sales, in the slicer I refer to it as Total Line Sales.
- In the matrix visual, because all the of dynamic column values are controlled by the Matrix Switch measure, they cannot be individually formatted - thus you have to pick a format for the entire measure that makes sense for all of your individual columns. I actually updated the solution file to use a general number format rather than a currency format, which was inappropriate for the Sales Rank column.
I initially thought I could solve this problem by wrapping each column measure within the Matrix Switch measure in a custom FORMAT statement. Unfortunately, when you do that it prevents the headers from disappearing dynamically.
I don’t think either of these are major problems if this dynamic column selection capability is something you want in your report, but just wanted you to be aware of them.