Microsoft Excel Power Pivot Advanced – DAX 1 day Level 2

Repetition of the Power pivot basic training

• Editing Imported Data
• Using multiple tables
• Creating pivot tables
• Build Calculating Columns in Power Pivot
• Make explicit measurements and calculations.
• Activating / Installing Power Pivot Microsoft Excel
• Difference between the Power Pivot data model and Microsoft Excel
• Importing data

The most used DAX Formulas

• max()
• if()
• sumx()
• countx()
• Calculate()
• all()
• sum()
• count()
• DistinctCount()
• min()

The following categories are covered

• Formatting and Formatting Numbers
• Time Intelligence Features
• Date and time functions
•  trigonometric and math functions
• Statistical functions
• text functions
• Filtering Features
• Information functions
• Logic functions

This list is not complete, a choice is made about what is discussed.

Convert to Cube functions

•  cube functions from Power Pivot table
• using the cube functions

Solve many to many relationships

• How is this resolved in Power Pivot?

Time and date functions for a DAX table

• startofmonth()
• startofquarter()
• startofyear()
• edit()
• month()
• WeekNumber()
• datesadd()
• datesvalue()
• endofyear()
• firstdate()
• firstnoblank()
• lastdate()
• lastnonblank()
• nextday()
• nextmonth()
• next quarter()
• next year()
• endofmonth()
• endofquarter()
• endofyear()
• yearfrac()
• Previousday()
• Previousmonth()
• Previousquarter()
• Previousyear()

Filter functions for a DAX table

• Relatedtable()
• lookupvalue()
• User Relationships()
• crossjoin()
• Distinct()
• keepfilters()

Information functions for a DAX table

• istext()
• islogical()
• isnontext()
• isnumber()

Logical functions for a DAX table

• switch()
• not()
• false()
• iferror()
• and()
• or()

Mathematical and Trigonometric Functions (DAX) table

• trunk()
• sumx()
• abs()

Statistical Functions (DAX) table

• max()
• maxx()
• max()
• min()
• Mina()
• minx()
• average()
• averagex()
• averagea()
• countblank()
• Countrows()
• count()
• counta()
• countx()
• Divide()

Text functions for a DAX table

• Concatenatex()
• Concatenate()
• lower()
• upper()
• White()
• Exact()
• fixed()
• format()

Functions for a time intelligence measurement Power Pivot

• closingbalancemonth()
• closingbalancequarter()
• closingbalanceyear()
• datesinperiod()
• Datesmtd()
• Datesqtd()
• datesbetween()
• openingbalancemonth()
• openingbalancequarter()
• openingbalanceyear()
• parallelperiod()
• totalmtd()
• totalqtd()
• totalytd()

Filter Functions for a DAX Measurement

• all()
• allexcept()
• Calculate()
• calculatetable()
• hasonevalue()
• allselected()
• value()

If you do not yet master the basic knowledge, it is better to first Microsoft Excel Power Pivot Basic training 1 day Level 1 Follow.

For more related training, please visit the Microsoft look at overview page, examples are the Microsoft Access Basic training 2 days Level 1, Microsoft Office Powerpoint Basic training 1 day Level 1, Microsoft Word Basic training 1 day level 1, Basic VBA Excel training (2 days) & Microsoft Excel Power Query training (1 Day)