book

Analyzing Data with Power BI and Power Pivot for Excel, First Edition

April 2017

Beginner to intermediate

256 pages

7h 54m

English

Read now

Unlock full access

Who this book is forAssumptions about youOrganization of this bookConventionsAbout the companion contentAcknowledgmentsErrata and book supportWe want to hear from youStay in touch
Working with a single tableIntroducing the data modelIntroducing star schemasUnderstanding the importance of naming objectsConclusions
Introducing header/detailAggregating values from the headerFlattening header/detailConclusions
Using denormalized fact tablesFiltering across dimensionsUnderstanding model ambiguityUsing orders and invoicesCalculating the total invoiced for the customerCalculating the number of invoices that include the given order of the given customerCalculating the amount of the order, if invoicedConclusions
Creating a date dimensionUnderstanding automatic time dimensionsAutomatic time grouping in ExcelAutomatic time grouping in Power BI DesktopUsing multiple date dimensionsHandling date and timeTime-intelligence calculationsHandling fiscal calendarsComputing with working daysWorking days in a single country or regionWorking with multiple countries or regionsHandling special periods of the yearUsing non-overlapping periodsPeriods relative to todayUsing overlapping periodsWorking with weekly calendarsConclusions
Introducing slowly changing dimensionsUsing slowly changing dimensionsLoading slowly changing dimensionsFixing granularity in the dimensionFixing granularity in the fact tableRapidly changing dimensionsChoosing the right modeling techniqueConclusions

Using data that you cannot aggregate over timeAggregating snapshotsUnderstanding derived snapshotsUnderstanding the transition matrixConclusions
Introduction to temporal dataAggregating with simple intervalsIntervals crossing datesModeling working shifts and time shiftingAnalyzing active eventsMixing different durationsConclusions
Introducing many-to-many relationshipsUnderstanding the bidirectional patternUnderstanding non-additivityCascading many-to-manyTemporal many-to-manyReallocating factors and percentagesMaterializing many-to-manyUsing the fact tables as a bridgePerformance considerationsConclusions
Introduction to granularityRelationships at different granularityAnalyzing budget dataUsing DAX code to move filtersFiltering through relationshipsHiding values at the wrong granularityAllocating values at a higher granularityConclusions
Computing multiple-column relationshipsComputing static segmentationUsing dynamic segmentationUnderstanding the power of calculated columns: ABC analysisConclusions
Understanding different scenariosMultiple source currencies, single reporting currencySingle source currency, multiple reporting currenciesMultiple source currencies, multiple reporting currenciesConclusions
TablesData typesRelationshipsFiltering and cross-filteringDifferent types of modelsStar schemaSnowflake schemaModels with bridge tablesMeasures and additivityAdditive measuresNon-additive measuresSemi-additive measures