Tagged: powerpivot; DAX; help
June 18, 2019 at 9:01 am #18009
- Started: 1
- Replies: 0
- Total: 1
I’m going to preface this post by saying that i’m very new to PowerPivot but that I’m hoping that someone is able to point me in the right direction for an issue that i’m encountering.
A bit of background: I manage an excel file which takes a large volume of client data and prepares basic and more complex data/insight/chart etc. that can be taken into powerpoint and presented to clients. Given that data that this is based on is circa 40,000 rows per wave of activity powerpivot presented a great solution to an issue of native Excel handling that volume of accumulated data.
I have an objective that I’ve laid out for myself which is to to provide a comparative figure within a single pivot table (something that i could not have done with normal Excel). For sake of a simple example I want to have an average ‘pass rate’ for one timeframe and compare this against another timeframe. I also want this timeframe to be controlled via sliders.
The below is the DAX measure that i have used to achieve a modicum of success:
% Passed:=[% of responses Passing] (datesbetween(‘MainTable'[Derived Date from Period],lastdate(TimeframeTable[Date From]),lastdate(‘TimeframeTable'[Date To])))
This is great, in that it relies on a linked table to pass the Date From and Date To from a pair of slicers to the DAX Measure that’s in my output pivot table. Lovely.
Unfortunately, my company has everyone utilising Excel 2010. So, whenever I use VBA to call an update to the pivot tables/caches it doesn’t actually do anything because this process is apparently (from what i’ve read) not really possible within the confines of the 2010/powerpivot relationship. Less lovely.
So (and to finally ramble onto my request) I have two questions:
- Is it possible to update a single linked table via VBA, or other simple action, without needing to use the work of Tom Gleeson ?(I could not get his solution to work and feel uncomfortable putting in place code that i cannot parse with my tiny brain)
- Is there an alternative way to pass a selected item from a Slicer/Cell/Shape or other element that sits within the actual workbook to a DAX measure?
I should also say that updating to Excel 2013 is not really an option at this stage. I’m not sure the upheaval for IT would be justified by my business case “I want slicers to work better”. 🙂
Please forgive me for the long write up. I hope that this complies with general forum etiquette and provides a reasonably comprehensive overview of my issue.
Thank you in advance for your help.
You must be logged in to reply to this topic.