Display Matrix values based on Slicer Selected Item only

Hi,

I would like to know if there is a way to achieve my requirement

Slicer : ProductName

Matrix : ProductName, Date, Sales

I need to show Matrix visual with all other productnames only when a specific item of productname is selected from the slicer

For ex: If I select Bags all products should show up in matrix with data and If I select Shoes, nothing should be displayed

Another ex : If I have 7 product names when I select 1 product name from slicer it should display all 7 product names in Matrix visual

Please let me know if there is a DAX way of achieving this

Thanks
Archer

@yendluru.aj,

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.

I hope this is helpful.

  • Brian

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

Please find attached ppt for exact details

Tier4Sample.pbix (638.3 KB)
TierSample.pptx (358.1 KB)

@yendluru.aj,

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:

https://exceleratorbi.com.au/show-or-hide-a-power-bi-visual-based-on-selection/

Forum - Show Hide Visual.pbix (2.0 MB)

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.

I hope this is helpful.

  • Brian

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

@yendluru.aj,

Would you mind providing a quick mockup of what you want each screen to look like given the different selection states for KPI?

Thanks.

  • Brian

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)

@yendluru.aj,

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.

  • Brian

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 :

  1. Use bookmarks (I’d like to understand a little better why this is not acceptable)
  2. 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…

@DaveC,

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…

  • Brian

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

@yendluru.aj,

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:

Unselected State:

image

Supply Plan Button Pressed:

Market Backorder Button Pressed:

See what you think. I hope this is helpful.

1 Like

Thanks Brian for the mock up solution and I will wait for @DaveC Hyperlink alternative before I propose or review it with my higher ups

Thanks for the clarification…

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.

    pagenav

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…

1 Like

@DaveC,

Fantastic analysis of the problem and alternatives. :clap:

I knew you’d be the right guy to loop in on this one. Thanks for taking the time on such a detailed response.

  • Brian

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!

Yes I have created a Button navigation panel within report and client was very happy
Navigate

1 Like