Learn to Write DAX: A practical guide to learning Power Pivot for Excel and Power BI

Learn to Write DAX: A practical guide to learning Power Pivot for Excel and Power BI

Language: English

Pages: 180

ISBN: 1615470417

Format: PDF / Kindle (mobi) / ePub

Learn to Write DAX: A practical guide to learning Power Pivot for Excel and Power BI

Language: English

Pages: 180

ISBN: 1615470417

Format: PDF / Kindle (mobi) / ePub


Active learning lessons for mastering DAX
 
Data analysis expressions (DAX) is the formula language of PowerPivot and this book is written to give hands-on practice to anyone who wants to become competent at writing such formulas. Sample exercises that explain each concept are provided and followed by practice questions and answers to maximize learning and experience with DAX.

CSS Secrets: Better Solutions to Everyday Web Design Problems

Doorway

Historical Dictionary of Architecture (Historical Dictionaries Of Literature And The Arts)

The Web Designer's Idea Book, Volume 4: Inspiration from the Best Web Design Trends, Themes and Styles

Pocket Guide to Combining Typefaces

1000 More Graphic Elements: Unique Elements for Distinctive Designs (1000 Series)

 

 

 

 

 

 

 

 

 

 

 

 

DISTINCTCOUNT() counts each value in a column once and only once. If the value appears more than once in a column, it is still counted only once. Consider the Customers table: In this case, the customer key is unique, and by definition each customer key appears only once in the table (note that customer key = customer number). So in this case, the DISTINCTCOUNT() of the customer key in the Customers table gives you the same answer as COUNTROWS() of the Customers table. But if you were to do a

context of a pivot table. When you do it this way, you can easily see how the [Total Global Sales] value is the same, regardless of the country in the pivot, and hence you can immediately see that you just need to divide the actually country sales by this total global sales amount, and it is going to work. Here is the final pivot, with some conditional formatting applied to make it easier to read: 12: DAX Topic: ALL(), ALLEXCEPT(), and ALLSELECTED() 95 Passing a Table or a Column to ALL()

Products[SubCategory] on Rows and then add [Total Sales Amount] and [Total Margin %] on Values. 2. Click inside the pivot table, navigate to the Power Pivot tab, and select KPIs, New KPI. 3. Select Margin % from the KPI Base Field (Value) drop-down (shown below). 17: Concept: KPIs and Multiple Data Tables 151 4. In the section Define Target Value, where you have two choices, select Absolute Value and set it to 0.45. (Note that 45% is actually 0.45; if you type 45 instead, it won't work.)

write is placed in the Sales table. 6. On the ribbon at the top of the page, click New Measure. 170 Learn to Write DAX 7. Write the [Total Sales] measure as shown below and press Enter. (Note that with Power BI, you need to specify the name of the measure and the formula in the formula bar as shown below.) Note There are some significant improvements in the Power BI user interface over the Excel interface, including the following: • You do not have to place a colon before the equal sign

Value] or [Total Margin %] = DIVIDE([Total Margin $] , [Total Sales Value]) 5. [Total Sales Tax Paid] = SUM(Sales[SalesTax]) 6. [Total Sales Including Tax] = [Total Sales Value] + [Total Sales Tax] 7. [Total Order Quantity] = SUM(Sales[OrderQuantity]) B. COUNT() These practice exercises appear in Chapter 4. 8. [Total Number of Products] = COUNT(Products[ProductKey]) 9. [Total Number of Customers] = COUNT(Customers[CustomerKey]) Note Counting the "key" columns is generally pretty safe

Download sample

Download