• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot Tutorial

Peter Bartholomew

Well-Known Member
I have gone back to my Kindle and discovered that I actually have a copy of PowerPivot by Ferrari and Russo I received as a gift. Since I have Office 2010 and, at the time, did not have the Power tools installed, I found it very heavy going! For me 'data' is half a dozen values that I put into a model to act as controls, so I wasn't even in the starting gate.

I was left with a feeling of Schizophrenia. On one hand, I was being told 'please don't use names, and even worse array formulas, out poor little darlings (their clients) won't understand them. The next moment reading convoluted descriptions of such complexity that I seriously wondered who on this planet was meant to understand what was going on!

That said, the features of Excel that you so articulately describe are exactly those that I find abhorrent. I hate the fact that Excel formulas are placed within a cell (and hence are many-to-one in terms of references) rather than being applied to a multi-cell range. Most business objects of any significance are either functions of time (say), represented by arrays, or lists, stored as tables with related attributes. Any significant operations are likely to apply at the level of the arrays or lists and it should be for Excel to carry the implementation down to cell level. After all, I can almost guaranty that I will create a multitude of errors if I attempt repetitive implementation tasks.

For me Dan Bricklin's most annoying decision was to recognise that variables could be named the programmer's way but instead opt for the second-rate town plan mapping stratagem that did not require the 'meaning' to be declared because to require the user to describe their data would be 'tedious'!
 

Peter Bartholomew

Well-Known Member
I did take a look at the cumulative total. It has more the look of a program than a single statement! I also noticed that it contains nested partial sums an so is not especially efficient. In Excel I would use the array formula

= IF( initialise.loan?,
loan.amount,
←debt.balance * ( 1 + interest.rate ) - debt.service )

where the "←" symbol is my notation to indicate that the reference applies to the previous time period (as an offset range). I think my uptake of DAX is likely to be slow but, hopefully, I will make progress.
 

NARAYANK991

Excel Ninja
Hi ,

Apropos your latest comment , I think you might find this heartening :

https://exceleratorbi.com.au/using-variables-dax/

http://tinylizard.com/var-best-thing-happen-dax-since-calculate/

As for your comment that DAX formulae look almost like short code segments , I think it is the same with me.

Generally on the Power Pivot forums , I see that people who have migrated to PP from an MDX background , find everything almost the same ; their learning curve is not so steep , whereas for those who have migrated from Excel , it takes some effort and relearning.

Narayan
 

Lori

Active Member
A key feature worksheet functions lack are the equivalents of FILTER and DISTINCT functions for working with arrays. Like others my experience of DAX is very limited and I hope to learn more of these powerful features.

Looking at the daxpatterns link, the cumulative quantity calculation is probably still easier done with formulas from my perspective:

Code:
Date     Qty
20-Dec-17  1
20-Dec-17  1
15-Jan-18  1
15-Jan-18  2
15-Feb-18  2
15-Feb-18  1
15-Mar-18  3
15-Mar-18  1
15-Apr-18  1
15-Apr-18  2

Cumulative Quantity :=
CALCULATE (
  SUM ( Transactions[Quantity] ),
  FILTER (
  ALL ( 'Date'[Date] ),
  'Date'[Date] <= MAX ( 'Date'[Date] )
  )
)
With worksheet functions:

CumulativeQty:=SUMIF(Date,"<="&Dates,Quantity)
Dates:=DATE(2018,{1;2;3;4;5},0)

To create the array of month end dates from the Date range you'd need a formula like:

Dates:=MODE.MULT(IF(FREQUENCY(Date-DAY(Date),Date),EDATE(Date-DAY(Date)+1,{1,1})-1))
 

NARAYANK991

Excel Ninja
Looking at the daxpatterns link, the cumulative quantity calculation is probably still easier done with formulas from my perspective:
Hi ,

I think where PP shines is when :

a. the data is voluminous

b. when multiple tables are involved

c. when there are relationships between the various tables

d. when the user is restricted to working with pivot tables and their filters and slicers , which in combination can be used to restrict the data to a very fine set of constraints.

Developing a user interface according to (d) using only formulae is bound to be a daunting task. PP can probably do it with just one measure.

Narayan
 
Top