You can use any MDX expression that returns a member as one of the expressions in the function. The next few examples show how you can use members directly in the name to get more functionality. However, if you look closely the CUBEVALUE function accepts CUBEMEMBER values. The method illustrated above is how the cells are made when a pivot table has been converted to formulas. The snapshot below shows how it looks in Excel when the cell with the CUBEVALUE function is selected: =CUBEVALUE(“AdvWorks”,$D8,F$7,Slicer_Commute_Distance) Because of this CUBEVALUE usually refers to a series of cells and slicers. For example, CUBEMEMBER is used in column and row headers in a typical solution. Typically, you would use these functions separately. Tip #18 demonstrates how to use cells in Excel. In this post, I will show how you can expand the CUBEVALUE function by directly using member expressions or CUBEMEMBER functions in the syntax. Cube functions allow us to apply MDX functionality in Excel beyond pivot tables and charts. If you have not looked at the cube functions in Excel check out two of my previous posts: Excel BI Tip #18: Using Cube Functions to Break Out of Pivot Tables and Excel BI Tip #27: CUBESET and CUBESETCOUNT Functions. I hope you too will discover something new you can use. In order to not forget what I learn or discover, I write it down … here. Much of the content will be a result of my daily interactions with business users and other BI devs. Keep in mind this issue is primarily a problem when using cube formulas in your Excel dashboard.Īs I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later. You will be able to create more creative dashboards with this technique. We will keep the date and table, but now we will see that the other slicers are now filtered to match the data that is available.Īs you can see, the solution is fairly simple, but not intuitive. Once these changes are applied, you will see how my data changed. Using the Apply Filters button on the Pivot Table ribbon, you need to select all the slicers you want to interact with each other. Step 2: Connect the Slicers to the Pivot Table In most cases, I recommend looking for a count type value that will always have data if there is a potential match of any kind. In my example, I will use the Total Ticket Amount as the value. You want to make sure that you do not unintentionally filter out slicers when data exists. The value you use in the pivot table, should only be “empty” or have no matches when that is the expected result. In order for this to work, you need to add a pivot table using the same connection you used with the slicers. Step 1: Add a Pivot Table with the Same Connection as the Slicers For sake of our example, I am going to put the pivot table in plain sight for the examples. In most cases we would add this to a separate worksheet and then hide the sheet from the users. To solve this issue, we are going to use a hidden pivot table. Solving the Problem with a “Hidden” Pivot Table Neither of these scenarios is user friendly and does not direct our users to see where the data matches. In the second example, we filter a seat which should cause the tables to hide values and it does not work as expected either.Īs you can see in the second example, we are able to select a seat that is either not related to the selected table or has no data on that date. In the first, you can see that the seats are not filtered. All of my slicers are set up to hide options when data is available. For example, when I select a date and a table, I expect to see my seat list reduce in size, but it does not. However, since we are using cube formulas the slicers have no ability to cross reference. When working with pivot tables or pivot charts, the slicers will hide values that have no matching reference. ![]() =CUBEVALUE(“ThisWorkbookDataModel”,$B6, Slicer_Date, Slicer_RestaurantName, Slicer_Seat_Number, Slicer_TableNumber)Įach cell with data references all the slicers. In my example, I have created a revenue distribution table using cube formulas such as: You have went to all the trouble to build out a good set of slicers which allow you to “drill” down to details based on selections. As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |