Please check out the solution I just posted below to a very similar question. I think the general approach laid out below should be fairly easily adaptable to your scenario:
If you still have problems after trying this, please post a PBIX file along with a mockup of your desired outcome, and we can work through a more specific solution.
Hi Brian
Thank you so much for your support ! The proposed solution is exlcude slicer selection from charts which is not desired state of visualization for my scenario.
Please find attached pbix which I have switched from direct query to import mode …
To give you background, this dashboard needs to be presented to senior execs
basically they want everything in one page and be able to switch visuals based on slicer selection ( they do not want bookmarks/button concept which is really annoying)
Based on KPI selection we need to switch to Line chart or Stacked Bar Chart
At the same time, based on KPI Selection we need to switch to Matrix and Table visuals
In the report, when KPI ‘LIFR’ is selected then it matrix should show up with all KPIs
When KPI ‘In Market back order’ is selected then it should show another matrix with irrelevant data should display which is quite huge in size
When KPI ‘Lead Time’ is selected then table visual should show up
Can this be achieved at all ? please advise
Coming to the issue I have raised, In the report, when KPI ‘LIFR’ is selected then matrix should show up with all KPIs this is where slicer selection of LIFR should pull all KPI and display it in Matrix
Okay, I understand your requirement a lot better now. I originally thought you were trying to control elements within a visual by slicer selection, but now I see you’re actually trying to control the whole visual itself. This would be easy to do with bookmarks and buttons, but as you indicate that’s not within the requirement. So, the only solution I can think of here is the use of overlaying cards on top of your visuals and then controlling the formatting of those cards (background color versus transparent) via a measure based on the slicer selection. This article by Matt Allington lays out exactly how to do that, and I’ve attached a sample PBIX from his post that shows this technique in action:
Also, I’m looping in @DaveC, who is a wizard with these type of visual workarounds in Power BI, to see if he has any additional ideas on how to handle this.
Hi Brian,
thanks for that direction, I have gone through Matt’s blog and I can see that it will only work if Chart axis dimension and slicer dimension are different. In my case, I need to be able to select a value from KPI slicer and then show all KPIs within Matrix chart
Yes its a whole process of conditional show/hide into play
@DaveC, Could you please shed some light on how best can we achieve my requirement
I started work in Manufacturing Operations Analytics recently it would be of great help if you can provide guidance
Hey Brian, I have attached the Powerpoint in which the screens are there based on KPIs
I am given the task to replicate those slides from powerpoint which were built out of excel dashboards into PowerBI
Here is it again TierSample.pptx (358.1 KB)
Okay, got it – thanks for the clarification. So, it wouldn’t be acceptable to replicate the look of the slicer exactly, but instead of the buttons being slicer selections, they would be actual buttons linked to direct page navigation actions (no bookmarks, since as of the March 2020 update you no longer have to do that via bookmarks)?
If that were an acceptable approach, I think that would make the solution really straightforward.
Unless I am missing something, it seems that this is just a need to show/hide different visuals. Although I wish it could, DAX cannot control the visibility of objects on the report page–it can only control what data is displayed inside a visualization that is already visible. In order to show or hide a visualization, you have two choices that I am aware of both of which have already been mentioned by @BrianJ :
Use bookmarks (I’d like to understand a little better why this is not acceptable)
Use buttons with page navigation. Depending on how you arrange each page, you can give the impression that they are not going to different pages.
A variation of number 2 above would be to use page navigation using hyperlinks instead of buttons to go to different pages of the report.
I have not created any sample PBIX files for this because I think I have understood from your posts that these are not acceptable solutions although I don’t understand why. If you are open to exploring these options further, we can get into some examples. If not, I’m not sure that I can be much help.
Please let me know if you want to explore these options further…
Thanks for confirming. The other possibility was a trick I first saw you use in putting transparent “masks” over visuals and changing the transparency via measure. That’s great for show/hide options, but it doesn’t work here for hide and replace, since to my knowledge there is no way to control “bring to front” or “send to back” via measure. Thus, the visual would “disappear” from one area and the new one would reappear in a different part of the screen. While it would be a humorous effect, it would be unlikely to please senior management…
Hi @DaveC, thanks for your reply … please see my responses and provide guidance
Archer : Can I use Hyperlinks on slicer ? if I can build something like Chiclet Slicer thats available on AppStore Marketplace where we click on a value and then it takes to relevant page provided we align the visuals uniformly across all pages
Check out the attached three page mockup I put together in the file attached below. The new navigation button options are incredibly flexible – far more so than the chiclet slicer (which I actually like and use a lot), including conditional formatting for all the button attributes.
In the mockup below, I built a simulated “slicer” (two buttons for simplicity) with a look very close to your slicer. By changing the default, hover and press options, and as @DaveC highlighted, by keeping the position of the “slicer” the same on each page you can give a nearly perfect illusion of the variable hide/show effect you’re looking for:
In the “Prebiult Nav.pbix” file you attached, there are two types of “navigation” that are occurring–page-to-page and hide/show objects on the same page.
To hide/show objects on the same page (which corresponds to the tabs across the top of the page), bookmarks are the only option I’m aware of.
To navigate from page to page (which corresponds to the buttons on the left side of the screen), the sample file you attached uses bookmarks also, but the March 2020 update to Power BI Desktop includes “Page Navigation” as a new button action. The result is identical to a bookmark that moves you to a new page, but the process is simplified because you no longer have to create a bookmark.
Hyperlinks (at least in the way that I was referring to them) are only possible in table cells (maybe matrix cells also, I haven’t tried that). That would only be useful if you want to have the user click on a text hyperlink in a table cell. It would not work with slicers. I made this suggestion before I noticed you chiclet slicer. I thought you want to show the visuals by clicking on the KPI names in the “Weekly Scorecard” matrix. If you were to try going down this route, we could discuss in more detail at that time. It’s definitely a “workaround” which would require adding a URL column to one of your tables, and you would have to have a way to insert the base URL for your published report into your dataset, so this approach is not without its challenges. Plus, you’d still have to manually create the destination pages for each hyperlink within the report. I would consider it a last resort.
After examining your PBIX file more closely, I believe that the reason you are trying to make this work with a slicer instead of buttons is so that it can be dynamic in the event that new KPIs are added to the “tier_kpis” table. Is that correct? If that is true, none of the proposed solutions so far are ideal because they would all require the creation of additional buttons and pages when new KPIs appear in your data. Unfortunately, I don’t know a way around that since you cannot dynamically show/hide objects nor change their visual properties (color, transparency, etc.) using DAX. If it were possible to use the same visuals for each set of KPI details and change the data being displayed for each, that would be straightforward, but showing/hiding visuals in a dynamic way that doesn’t require editing the PBIX file to create new buttons and/or pages when new KPIs are added is not something I have a solution for.
This is probably not the news you wanted, but I’m afraid it is the current reality of Power BI as far as I know.
Having said all of this, if it is acceptable to use buttons and page navigation, @BrianJ 's simulated chiclet slicer or using the new “page navigation” action on buttons or images are probably your best bet. You would just have to create new buttons and pages each time a new KPI and corresponding details were added to the report.
I hope I addressed all your points/questions. If I missed something, let me know…
Hi @yendluru.aj, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
