XL book, Supercharge Excel when you learn to write DAX for power pivot

20210207 1506 

 

reference

[1] Supercharge Excel when you learn to write DAX for power pivot, 2018, Matt Allington

 

motivation

(1) I want to make a new column, using existing fields in a table. ⇒ in Excel, you should make a new column, calculating values from existing fields. this need more memory space and time to calculation. and every time you update the original table, you should update the field too. 

⇒ in pivot field, the calculated field or derived field can be made. but DAX can do

(2) Merging or relating two table. In traditional Excel, you should use "vlookup" to relate two table. and this make the merged file get bigger and bigger as you link more items to it, by using "vlookup".

⇒ power pivot can do without making files get bigger. ; [1] p.1

 

what is in the book

 

▶ concept : loading data; [1] p.3

 

▶ two types of table : lookup table vs data table ; [1] p.17

- lookup table : calendar, costumer, products

- data table : sales

 

▶ joining tables in power pivot; [1] p.13

⇒ drag and drop from data table to lookup table, matching filed to field..

 

▶ concept : measures; [1] p.25

 

▶ Writing DAX measures; [1] p.25

 

▶ SUM, COUNT, COUNTROWS, MIN, MAX, COUNTBLANK, DIVIDE ; [1] p.34

- a column as the only parameter

- these functions are aggregators* : convert a column of values into a single value

--> using a "naked" column, makes error

--> you have to tell Power Pivot how to aggregate the data ...so that it returns a single value

* aggregator에 상대되는 개념은 iterator이다. see below([1] p.57)

 

▶ Practice exercises : SUM ; [1] p.36

 

▶ concept : filter progagation ; [1] p.51

 

▶ data tables, lookup tables, flattened tables ; [1] p.57

 

▶ Iterators ; [1] p.62