Learn to Write DAX: A practical guide to learning Power Pivot for Excel and Power BI
Format: PDF / Kindle (mobi) / ePub
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.
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