![]() ![]() $M$1 is the cell where I created the CUBESET() formula, and I do not want that reference to autoadjust.– so that each cell grabs the first, second, third, etc. ROW(A1) returns 1, and when I fill the formula down, I get ROW(A2), ROW(A3), etc.The IFERROR is used to handle the case where the user has not selected that many slicer tiles – CUBERANKEDMEMBER returns an error when you “fall off the edge” of the user’s selections.I then fill this formula down enough cells to handle every slicer tile (about 26 cells down in this case).Now I write a CUBERANKEDMEMBER() formula:ĬUBERANKEDMEMBER() Wrapped in an IFERROR() I set it to “This is My Set” so that I can see where the set lives. To see that, click on the slicer, and check its options dialog:įinding the Name of the Slicer to Use in Your Formulaģ) The third argument can be left blank, but then you get a blank cell in your sheet. In 2013, I think it may be “Data Model” but I’d have to double check to be sure.Ģ) The second argument is the “formula-approved name” for your slicer. =CUBESET(“PowerPivot Data”, Slicer_Description3,”This is My Set”)ġ) In PowerPivot v1 and v2, the first argument will always be “PowerPivot Data”. Go download it and come back CUBESET!įirst step is to write a single CUBESET() formula: This problem is SO much easier to solve with PowerPivot (or Excel 2013) than “normal” Excel. If you’re not using PowerPivot yet, here’s yet another good reason to get started. This Technique Only Works With PowerPivot or Excel 2013! ![]() Kasper’s post above (#1) is a good example of catching in DAX, as is ANY post dealing with disconnected slicers. Other times you want to catch them and use them in “normal” Excel formulas in a worksheet. Right up front, I want to make a distinction: sometimes you want to catch a user’s slicer selections and use those in a measure. Catching in Excel: This Post is About the Latter Today I am going to correct that omission.Ĭatching in DAX vs. I’ve never been terribly comfortable with that third post in particular, the one dealing with multiple selections.įunny thing is, I used a new technique (for me) in the Calendar Chart posts, but never went back and called it out explicitly. There have also been a number of comments on each post that suggested alternate (and often better) ways of doing things. No, I don’t mean things like “Fox Urine” or “Face to Anogenitaled” – those are pretty funny of course, and they come up in my job because I consult for my scientist neighbor on his lab rat projects.īut no, I’m here to talk about something even more popular than Fox UrineĮvery day, one of the most-read topics on this blog is some variant of “I want to catch slicer selections in formulas.” This has been covered in at least three different posts: IF You Are Using PowerPivot (AKA the 2013 Data Model Feature) A Popular Topic It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |