CRPBI

Data Analysis & Reporting using Power BI

→ Beware of the “Naked” Columns when writing measures
→ Difference between referencing a column and a measure
→ Assign the measures to the relevant table
→ CALCULATE(), The basis of (almost) all the work in PowerPivot
→ Syntax
→ Example
→ Another one
→ Yet another Example
→ Additional Alternatives
→ The “ALL” Function (a.k.a. The “filterremover”)
→ The ALL() Benefits
→ 1.Percentage of Parent
→ 2.Negating the Slicers
→ Important
→ ALLEXCEPT()
→ ALLSELECTED()
→ Working with multiple tables
→ Lookup Tables and Data Tables
→ Viewing the relationships in Diagram view
→ CALCULATE() goes through relationships also
→ INTERMISSION
→ Disconnected Tables
→ Add the Parameters
→ Add a Harvester Measure
→ Have you consulted Field List?
→ Parameters are friends of Rows and Columns
→ Variations in Disconnected Tables
→ Filter()
→ Working rules
→ Thresholds with disconnected tables
→ Validation
→ Threshold with min and max boundaries

→ Time Intelligence
→ Calendars
→ How to create a calendar table
→ What you need in a calendar
→ Getting started
→ Special Features of Calendar Table
→ DATESYTD()
→ Can the year-end date be changed?
→ DATESMTD() and DATESQTD()
→ TOTALYTD()
→ FIRSTDATE() and LASTDATE()
→ ENDOFMONTH(), STARTOFYEAR(), etc.
→ CLOSINGBALANCEMONTH(), CLOSINGBALANCEYEAR(), etc.
→ DATEADD()
→ Growth Since Prior Year (YOY)
→ Some Drawbacks of DATEADD()
→ SAMEPERIODLASTYEAR()
→ PARRALLELPERIOD()
→ NEXTMONTH(), PREVIOUSYEAR(), etc.
→ DATESBETWEEN()
→ Life To Date()
Case: practical example

→ Using powerful DAX functions that will facilitate lots of dynamic modelling
→ IF()
→ BLANK()
→ DIVIDE()
→ ISBLANK()
→ HASONEVALUE()
→ More IF()
→ VALUES()
→ SWITCH()
→ SWITCH TRUE()
→ The “X” Functions (also called “Iterator” Functions)
→ SUMX()
→ MINX(), MAXX() and AVERAGEX()
→ FILTER()
→ COUNTX() and COUNTAX()
→ “X” Functions work with the “absentee” also
→ RANKX()
→ TOPN()
→ Multiple Data Tables
→ Merging two data tables into one Pivot
→ “Hybrid” Measures
→ Few Points to remember
→ Data Tables with different “Granularity”
→ Budget vs. Actuals Case: putting it together

→ How to Install
→ Getting started
→ Creating the most amazing and dynamic dashboards
→ Very valuable visualizations
→ Changing the controls of your different visualizations
→ Controlling the whole dashboard or page by page
→ Downloading new visualizations from Microsoft
→ What if analysis
→ Different dashboards for different users
→ Cross referencing
→ Drill in and drill out
→ Hierarchy
→ Map showing your biggest sales region
→ Going through the performance of your products one by one
→ A pyramid of all your costs
→ Your users can get charts and data using Q&A just like Google
→ Get data from literally any source
→ Work on the tables, just like PowerPivot
→ Manage Relationships
→ New Measure
→ Calculated Columns can also be created in the same way, by right-clicking in the Field list
→ Creating Reports
→ Import already prepared PowerPivot Models
→ Publishing your dashboards to cellphones, tablets and desktops
→ Dashboard, amazing dynamic examples
→ Sharing the dashboard in various ways
Case: practical case in power BI