Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Extreme DAX
  • Table Of Contents Toc
  • Feedback & Rating feedback
Extreme DAX

Extreme DAX

By : Michiel Rozema, Henk Vlootman
4.9 (44)
Buy this Book
close
close
Extreme DAX

Extreme DAX

4.9 (44)
By: Michiel Rozema, Henk Vlootman
Buy this Book

Overview of this book

This book helps business analysts generate powerful and sophisticated analyses from their data using DAX and get the most out of Microsoft Business Intelligence tools. Extreme DAX will first teach you the principles of business intelligence, good model design, and how DAX fits into it all. Then, you’ll launch into detailed examples of DAX in real-world business scenarios such as inventory calculations, forecasting, intercompany business, and data security. At each step, senior DAX experts will walk you through the subtleties involved in working with Power BI models and common mistakes to look out for as you build advanced data aggregations. You’ll deepen your understanding of DAX functions, filters, and measures, and how and when they can be used to derive effective insights. You’ll also be provided with PBIX files for each chapter, so that you can follow along and explore in your own time.
Table of Contents (17 chapters)
close
close
close
Preface
Icon
Preface
Icon
Who this book is for
Icon
What this book covers
Icon
To get the most out of this book
Icon
Get in touch
Free Chapter
1
Part I: Introduction
Icon
Part I: Introduction
2
1.1 DAX in Business Intelligence
Icon
1.1 DAX in Business Intelligence
Icon
The Five-Layer model for business intelligence
Icon
Enterprise BI and end-user BI
Icon
Where DAX fits in, and where to find it
Icon
Tools to develop models and DAX
Icon
Powered by DAX: visual, interactive reports
Icon
How to approach solution development
Icon
The digital transformation cycle
Icon
Summary
3
1.2 Model Design
Icon
1.2 Model Design
Icon
Columnar data storage
Icon
Data types and encoding
Icon
Relationships
Icon
Effective model design
Icon
Memory and performance considerations
Icon
Summary
4
1.3 Using DAX
Icon
1.3 Using DAX
Icon
Calculated columns
Icon
Calculated tables
Icon
Measures
Icon
DAX security filters
Icon
DAX queries
Icon
Date tables
Icon
Best practices in DAX
Icon
Summary
5
1.4 Context and Filtering
Icon
1.4 Context and Filtering
Icon
The Power BI model
Icon
Introduction to DAX context
Icon
DAX filtering: Using CALCULATE
Icon
Time intelligence
Icon
Changing relationship behavior
Icon
Table functions in DAX
Icon
Filtering with table functions
Icon
DAX variables
Icon
Summary
6
Part II: Business cases
Icon
Part II: Business cases
7
2.1 Security with DAX
Icon
2.1 Security with DAX
Icon
Introduction to row-level security (RLS)
Icon
Securing hierarchies using PATH functions
Icon
Securing attributes
Icon
Securing aggregation levels
Icon
Summary
8
2.2 Dynamically Changing Visualizations
Icon
2.2 Dynamically Changing Visualizations
Icon
The business case
Icon
Dynamic measures
Icon
Dynamic labels
Icon
Combining dynamic labels and dynamic calculations
Icon
Summary
9
2.3 Alternative Calendars
Icon
2.3 Alternative Calendars
Icon
Week-based and Gregorian calendars
Icon
Creating a week-based calendar table
Icon
Time Intelligence calculations for week-based calendars
Icon
Keeping your report current
Icon
Summary
10
2.4 Working with AutoExist
Icon
2.4 Working with AutoExist
Icon
The Power BI model
Icon
How Power BI visualizes the output of a model
Icon
What AutoExist is, and what it does
Icon
Example: The case of the missing workdays
Icon
How to solve the missing workdays problem
Icon
Optimizing report performance with AutoExist
Icon
Summary
11
2.5 Intercompany Business
Icon
2.5 Intercompany Business
Icon
Modeling the QuantoBikes sales process
Icon
Business between subsidiaries
Icon
Future sales
Icon
Summary
12
2.6 Exploring the Future: Forecasting and Future Values
chevron up
Icon
2.6 Exploring the Future: Forecasting and Future Values
Icon
Financial calculations
Icon
Financial DAX functions
Icon
The business case and model
Icon
Calculating Future Value (FV)
Icon
Calculating Net Present Value (NPV)
Icon
Calculating the Internal Rate of Return (IRR)
Icon
Calculating cost-covering rent
Icon
Summary
13
2.7 Inventory Analysis
Icon
2.7 Inventory Analysis
Icon
Data modeling for status-oriented data
Icon
Basic inventory calculations
Icon
Inventory forecasting
Icon
Summary
14
2.8 Personnel Planning
Icon
2.8 Personnel Planning
Icon
The Power BI model
Icon
Calculating sales
Icon
Calculating FTEs needed
Icon
Optimizing the Power BI model
Icon
Considering aggregation levels
Icon
Summary
15
Other Books You May Enjoy
Icon
Other Books You May Enjoy
16
Index
Icon
Index

Calculating cost-covering rent

The last topic in this chapter is about cost-covering rent, or CCR for short. This is the answer to the question: what is the minimum rent to charge property tenants to make the investment break even?

Let's go back to the NPV formula once again:

The IRR is calculated by solving this formula for the rate r at zero NPV:

The cost-covering rent also comes from solving the NPV formula, but now for (roughly) the future values:

To be more precise, while our future values are composed of multiple components, we only want to solve for the (initial) recurring positive cash flows in this case. The question is therefore: instead of the positive recurring cash flows in the fPosCashFlows table, and specifically those with Type 2 (rent), which values can we use to get a zero NPV?

Like with the IRR, there is no direct way of calculating this. And worse, there is no DAX function that will solve this equation. We therefore need...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 8
Next Section

Create a Note

Modal Close icon
You need to login to use this feature.
notes
bookmark search playlist download
font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Delete Note

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY