The client and I brainstormed, and we decided to create card visuals to identify filter selections. Truth be told though, it only partially matters what we think. I was working on a project for a client, and needed to create a Power BI report for them. Jan Karel Pieterse info jkp-ads.Today I want to talk to you about a real-world business problem and solution I encountered last year. Showing selected Slicer items on a worksheet Even though it is often sufficient to see the slicer on the workbook it can be useful to be able to get a list of the filtered sliceritems in a worksheet cell.

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy. Please enter your name required : Your e-mail address optional, will only be used to inform you when your comment is published or to respond to your question directly : Your request or comment max characters. I find Slicers a real gem in Excel and A great addition to the product! If your question is not directly related to this web page, but rather a more general 'How do I do this' Excel question, then I advise you to ask your question here: www. Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. In a worksheet cell the formula would look like this. The function takes the slicername as its argument and returns a text string comma delimited containing the selected items. See the screenshot below note the formula has as the delimiter, many users need to use the comma instead. The name you are looking for is marked in red in the screenshot below and indicated by 'Name to use in formulas'. This name can be shown in the userinterface by right-clicking the slicer in question and selecting 'Slicer settings'. Even though it is often sufficient to see the slicer on the workbook it can be useful to be able to get a list of the filtered sliceritems in a worksheet cell.

Now we will use the selections (the table, we have created) in a slicer, and will create an Area chart to show the slicerselections by month.Subscribe in a reader. To get the true value, which in this case will be a calculated value of the selective slicer value, we used the SWITCH(TRUE(),) function. Instead of using the SELECTEDVALUE formula multiple times, we are using a variable as “Selection” which makes it easier to look at. Domestic and International) using profit data from the Sample report.įor Domestic: Domestic = CALCULATE(SUM('financials'),'financials'= 'Canada')įor International: International = CALCULATE(SUM('financials'),'financials''Canada')Īgain, we will create another measure to get the selected value from the slicer.

Now, we will create two measures for these selections options (i.e.

Excel formula get slicer value