PowerPivot Help

• Dec 19th, 2016, 03:17 PM
Matt Allington
Re: PowerPivot Help
Well I hope so :-). The key to self service BI is having the skills to write he DAX and the business knowledge in the one brain. The more you learn, the easier it becomes of course. Good luck.
• Dec 21st, 2016, 01:01 PM
jgedwardsv
Re: PowerPivot Help
Matt -

I have another question about calculated columns.

With the data sample that I uploaded on the participant table, there are several columns that I calculate in excel. Would it be possible to handle those calculations in powerpivot to remove the ability for someone to change my formulas? For instance, I know how to calculate the age, but ran into some issues with calculating the ss benefit estimate. I'm guessing this is because I am using the sumproduct formula which isn't available in powerpivot. Also, I wasn't able to find a fv formula alternative either which is necessary to calculate the future account value.

In a sense, what I would like to do is remove anything from the data model that someone (other than me) should have the ability to change in excel.

John
• Dec 21st, 2016, 04:05 PM
Matt Allington
Re: PowerPivot Help
Technically anyone that knows what they are doing can change any formulas. If you are importing it from Excel (separate workbook) and you do the precalc in Excel, then they can't change it if they can't see it. So in a sense doing it in Excel may be more secure.

However even if you do it in Excel, anyone can still change it in Power Pivot. E.g. If you have following measure running of a column you calculated in Excel
commission = sum(table[payments])

then anyone could change the formula to something like this.
commission = if(hasonevalue(users[name]),if(values(users[name])="Matt Allington",sum(table[payments])*1.5,sum(table[payments])))
which basically checks the user and pays me 50% more than it should. (Note the dax may not be perfect, but the point is that it could be done. )

regarding sumproduct, sumx is very similar (If you write it as a measure). But there is so much to learn and I still don't really understand your data. My book has a section on sumx and you can read this article on my blog When to use SUM vs SUMX in DAX