### Unlimited Access

For Registered Users

To contact us, you can contact us via the following mobile numbers by calling and WhatsApp

+989115682731 Connect To WhatsApp

+989917784643 Connect To WhatsApp

For Registered Users

100% Secure Payment

10 Days Returns

Call Us Anytime

edition: [2nd edition] Authors: Collie. Rob, Singh. Avi serie: ISBN : 9781615470396, 9781615472260 publisher: Holy Macro! Books publish year: 2016 pages: [334] language: English ebook format : PDF (It will be converted to PDF, EPUB OR AZW3 if requested by the user) file size: 16 Mb

price : $8.25 11 With 25% OFF

Power Pivot and Power BI Dedications Supporting Workbooks and Data Sets Errata and Book Support A Note on Hyperlinks Foreword and Forward “State of the Union” November 2015 – What’s Changed? What Has Changed at Microsoft? Virtually Everything. What’s Changed in My Corner of the World? Also Everything. Introduction - Our Two Goals for this Book 1 - A Revolution Built On YOU Does This Sound Familiar? Excel Pros: The World Is Changing in Your Favor Our Importance Today Excel at the Core Three Ingredients of Revolution Ingredient One: Explosion of Data Ingredient Two: Economic Pressure Ingredient Three: Dramatically Better Tools 2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions It’s a Family of Products Built on Shared Engines Power Query is a Close Second in Importance Visuals: The Crucial “Last Mile” Power BI Desktop: Two Tools for the (Learning) Price of One! Same Engines, Just Different Visuals What do we mean by the “tough” or “valuable” stuff? Power Pivot (in Excel) Versions Power Pivot for Excel 2010 Power Pivot for Excel 2013 - Only Available in “Pro Plus” Excel Differences in User Interface: 2010, 2013, 2016 When We Said “Cosmetic” We Meant “Awkward” 32-bit or 64-bit? Office 2010 or Newer is Required 3 - Learning Power Pivot “The Excel Way” Power Pivot is Like Getting Fifteen Years of Excel Improvements All at Once Learn Power Pivot As You Learned Excel: Start Simple & Grow When to Use Power Pivot, and How it Relates to Normal Pivot Usage What This Book Will Cover in Depth 4 - Loading Data Into Power Pivot No Wizards Were Harmed in the Creation of this Chapter Everything Must “Land” in the Power Pivot Window Launching the Power Pivot Window One Sheet Tab = One Table You Cannot Edit Cells in the Power Pivot Window Everything in the Power Pivot Window Gets Saved into the Same XLSX File Many Different Sources Linked Tables (Data Source Type) Advantages Limitations Tips and Other Notes Pasting Data Into Power Pivot (Data Source Type) Advantages Limitations Importing From Text Files (Data Source Type) Advantages Limitations Databases (Data Source Type) Advantages Limitations Less Common Data Source Types SharePoint Lists Reporting Services (SSRS) Reports Cloud Sources Like Azure DataMarket and SQL Azure “Data Feeds” Other Important Features and Tips Renaming up Front – VERY Important! Don’t Import More Columns than You Need Table Properties Button Existing Connections Button 5 - Intro to Calculated Columns Two Kinds of Power Pivot Formulas Adding Your First Calculated Column Starting a Formula Referencing a Column via the Mouse Referencing a Column by Typing and Autocomplete Just like Excel Tables! Rename the New Column Reference the New Column in Another Calculation Properties of Calculated Columns No Exceptions! No “A1” Style Reference Stored Statically with the File Slightly More Advanced Calculations Function Names Also Autocomplete Aggregation Functions Implicitly Reference the Entire Column Quite a Few “Traditional” Excel Functions are Available Excel functions Are Identical in Power Pivot Enough Calculated Columns for Now 6 - Introduction to DAX Measures “The Best Thing to Happen to Excel in 20 Years” Aside: A Tale of Two Formula Engines Adding Your First Measure Create a Pivot Add a Measure! Name the Measure Results Works As You Would Expect “Implicit” Versus “Explicit” Measures Referencing Measures in Other Measures Another Simple Measure First Creating a Ratio Measure Original Measures Do NOT Have to Remain on the Pivot Changes to “Ancestor” Measures Flow Through to Dependent Measures Cases Where This Makes Real Sense Reuse Measures, Don’t “Redefine” Other Fundamental Benefits of Measures Use in Any Pivot Centrally-Defined Number Formatting Whetting Your Appetite: COUNTROWS() and DISTINCTCOUNT() COUNTROWS(Sales) DISTINCTCOUNT(Sales[OrderDate]) Deriving More Useful Measures From These Two Rearrange Pivot, Measures Automatically Adjust! Slicers in Different Versions of Excel Measures Are “Portable Formulas” 7 - The “Golden Rules” of DAX Measures How Does the DAX Engine Arrive at Those Numbers? Stepping Through That Example Translating the Examples Into Three Golden Rules Rule A: DAX Measures Are Evaluated Against the Source Data, NOT the Pivot Rule B: Each Measure Cell is Calculated Independently Rule C: DAX Measures are Evaluated in 6 Logical Steps Step 1: Detect Pivot Coordinates Step 2: CALCULATE Alters Filter Context Step 3: Apply Those Filter Coordinates to the Underlying Table(s) Step 4: Filters Follow the Relationship(s) Step 5: Evaluate the Arithmetic Step 6: Return Result How the DAX Engine Calculates Measures No “Naked Columns” in Measure Formulas Best Practice: Reference Columns and Measures Differently Best Practice: Assign Measures to the Right Tables 8 - CALCULATE() – Your New Favorite Function A Supercharged SUMIF() CALCULATE() Syntax CALCULATE() in Action – a Few Quick Examples How CALCULATE() Works Two Useful Examples of CALCULATE() Example 1: Transactions of a Certain Type Example 2: Growth Since Inception Alternatives to the “=” Operator inEvaluation of Multiple in a Single CALCULATE() The “ALL” (aka “Unfiltered”) Filter Context Not all Totals Are Completely (or Even Partially) Grand 9 - ALL() – The “Remove a Filter” Function The Crisp Basics The Practical Basics – Two Examples Example 1 – Percentage of Parent Example 2 – Negating a Slicer Variations ALLEXCEPT() ALLSELECTED() 10 - Thinking in Multiple Tables A Simple and Welcome Change Unlearning the “Thou Shalt Flatten” Commandment Relationships Are Your Friends “Lookup” Tables The Diagram View Using Related Tables in a Pivot Why That Works: Filter Context “Travels” Across Relationships Visualizing Filters Flowing “Downhill” – One of Our Mental Tricks Filters from All Related Lookup Tables Are Applied CALCULATE() Also Flow Across Relationships 11 - “Intermission” – Taking Stock of Your New Powers 12 - Disconnected Tables A Parameterized Report Adding the Parameter Table Adding a “Parameter Harvesting” Measure The Field List is Grumpy About This Using the Parameter Measure for Something…Useful Parameter Table Can Be Used on Rows and Columns Too! Why is it Important That They Be Disconnected? A Very Powerful Concept Disconnected Table Variation: Thresholds Create a Disconnected Table to Populate the Slicer: Write a Measure to “Harvest” the User’s Selection: Diverging From the Prior Example: We Need to Filter, Not Perform Math CALCULATE() Has a Limitation? Not really. 13 - Introducing the FILTER() Function, and Disconnected Tables Continued When to Use FILTER() FILTER() Syntax Why is FILTER() Necessary? It’s All About Performance (Speed of Formula Evaluation) How to Use FILTER() Carefully Applying FILTER() in the “Thresholds” Example Revisiting the Successful Formula Verifying That the Measures Work This Could Not Be Done with Relationships Tip: Measures Based on a Shared Pattern – Create via Copy/Paste More Variations on Disconnected Tables Upper and Lower Bound Thresholds Fixing the Sort Order on the Slicer: The “Sort By Column” Feature Completing the Min/Max Threshold A Way to Visualize Disconnected Tables Putting This Chapter in Perspective 14 - Introduction to Time Intelligence At Last, It is Time! “Standard Calendar” versus “Custom Calendar” Standard Calendars: The Focus of This Chapter Custom Calendars: Perhaps Even More Important than Standard (Covered Later) Calendar: A Very Special Lookup Table Where to Get a Calendar Table Properties of a Calendar Table Our Calendar table: Imported and Related Operates like a Normal Lookup Table First Special Feature: Enable Date Filtering via Mark as Date Table Second Special Feature: Time Intelligence Functions! Diving in with DATESYTD() Anatomy of DATESYTD() Function Definition How Does it Work? Changing the Year-End Date DATESMTD() and DATESQTD() – “Cousins” of DATESYTD() TOTALYTD() – Another Cousin of DATESYTD() The Remaining (Many) Time Intelligence Functions – Grouped Into “Families” FIRSTDATE() and LASTDATE() ENDOFMONTH(), STARTOFYEAR(), etc. DATEADD() Growth Versus Last Year (Year-Over-Year, YOY, etc.) Quirks and Caveats You Must Have Contiguous Date Ranges on Your Pivot DATEADD() Has Special Handling for “Complete” Months/Quarters/Years DATEADD() Lacks Intelligence for Weeks SAMEPERIODLASTYEAR() PARALLELPERIOD(), NEXTMONTH(), PREVIOUSYEAR(), etc. PARALLELPERIOD() NEXTMONTH(), PREVIOUSYEAR(), etc. DATESBETWEEN() “Life to Date” Calculations Removing That Hardwired 1/1/1900 DATESBETWEEN() is Fantastic with Disconnected Tables Too! 15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun Using IF() in Measures The BLANK() Function DIVIDE() Function The ISBLANK() Function HASONEVALUE() IF() Based on Row/Column/Filter/Slicer Fields The VALUES() Function Using VALUES() for Columns That Are Not on the Pivot VALUES() Only Returns Unique Values SWITCH() SWITCH TRUE() 16 - SUMX() and Other X (“Iterator”) Functions Need to Force Totals to Add Up “Correctly?” Anatomy of SUMX() SUMX() in Action Detailed Stepthrough MINX(), MAXX(), AVERAGEX() FILTER() COUNTX() and COUNTAX() Why is This Different From COUNTROWS(), Then? COUNTAX() versus COUNTX() Using the X Functions on Fields That Aren’t Displayed But Which Country? RANKX() The Use of ALL() Ties The Optional Parameters Duplicate FullNames? TOPN() Non-Measure Second Arguments to the X Functions The COUNTAX() Mystery Solved! 17 - Multiple Data Tables Service Calls Service Calls and Sales Mashup In Traditional Excel Do Not “Flatten” Measures from Different Data Tables in the Same Pivot! Hybrid Measures Multiple Data Tables Gotchas Using Fields from Lookup Table vs. the Data Table Data Table Connected to Some but Not All Lookup Tables Staying Out of Trouble 18 - Multiple Data Tables – Differing Granularity Example1: Budget versus Actuals Difficult in Normal Excel Much Faster and More Flexible in Power Pivot Creating Relationships – We Need Some New Lookup Tables Where Do We Get This New Lookup Table? Consider a Database or Power Query SalesTerritory is at Same Granularity Already Repeating the “New Table” Process for Calendar Integrated Pivot Hybrid Measures with Data at Different Grain Example 2: Using that Mysterious RANKX() Third Argument The Problem: Ranking MY Products Against Theirs! Year Granularity Mismatch Means a New Lookup Table Simple Measure Now the Absolutely Amazing “Cross-Rank” Measure And Since Both Are Filtered by the Years Table… 19 - Performance: Keep Things Running Fast How Important is Speed? "Now" Is Three Seconds in Length What Happens When Something Takes Longer Than Three Seconds? Slicers: The Biggest Culprit “Cross-Filtering” Behavior Cross-Filtering is Expensive in Terms of Performance Mitigating the Effects of Cross-Filtering How to Turn off Cross-Filtering Turning off Cross-Filtering Only Impacts that Slicer Slicers For Which You Should Turn Cross-Filtering Off The Shape of Your Source Tables Is Also Important Narrower Tables are Better Imported Columns Are Generally Better than Calculated Columns “Star Schema” is Generally Better than “Snowflake Schema” Measure Performance DISTINCTCOUNT() is Much Faster than COUNTROWS(DISTINCT()) FILTER() Should Only Be Used Against Lookup Tables and Other “Small” Columns Remember That the “X” Functions Are Loops 20 - Power Query to the Rescue Power Query: Bring Order to Messy Data #1 - Appending Files to Create a Single Power Pivot Table Scenario Connecting to One of the CSV Files Adding a Custom Column to “Tag” This File Loading the Data into Power Pivot Connecting to the Second CSV File Connecting to the Third CSV File Time for the Append! “Keeping” Only the Appended Query Testing Refresh Why This Is a Major Benefit #2 - Combine Multiple Files from a Folder into a Single Table Scenario From Folder Combine CSV Files First Row As Headers Change Data Type and Remove Errors Testing Refresh Why This Is a Major Benefit #3 – Adding Custom Columns to Your Lookup Tables Scenario Get Data Add Custom Column Define Custom Formula Why This Is so Amazing #4 - Using Power Query to “Unpivot” a Table Scenario Get Data from Excel Header Row Handling and Remove Column Unpivot! Rename and Change Type Why This Is a Major Benefit #5 - Using Power Query to Create a Lookup Table from a Table Scenario Create Lookup Table Create Data Table Relating the Two Tables Why This Is so Amazing #6 - Creating a Calendar Table: Advanced Power Query “Wait, I Don’t See a ‘Make Calendar’ Button!” Steps Why This Is a Major Benefit How NOT to Use Power Query Don’t Use Power Query Without Power Pivot Don’t Use Power Query Calculations as a Substitute for DAX Measures Don’t Use Power Query to Flatten Tables Don’t Use Power Query to Mash Two Data Tables Together 21 - Power BI Desktop Meet the New Kid On the Block Tons of Visualizations Creating Reports is Easy as 1-2-3 Fully-Interactive Reports Make Your Data Come to Life Power Pivot, Power Query and Power View++ All in One Package Download Today! Remember: Same Engines, Different Visuals A Few Words of Perspective You’re Probably Going to Use Both The “Sales Pitch” – Show Excel-Based to the Analysts, Desktop to Execs The “Tour” Missing Terminology The Different Modes Get Data (a.k.a. Power Query) Data Model (a.k.a. Power Pivot) Reports (a.k.a. Power View, but Much Better!) Import Existing Excel Power Pivot Models! Sharing Power BI Desktop Files 22 - “Complicated” Relationships Multiple Relationships Between the Same Two Tables USERELATIONSHIP() Many to Many Relationships First, a Bad Example Another Bad Example Real-World Overlap: The Source of “Legit” Many-to-Many “Bridge” Table Apply M2M as a Pattern Power BI Desktop 23 - Row and Filter Context Demystified The Basics: Gateway to Doubling Your Superpowers The Simple Definitions Row Context: The Key Ingredient of Calc Columns There’s No Row Context in Measures! Filter Context: The Key Ingredient of Measures There’s No Filter Context in Calc Columns! Recap So Far Interaction with Relationships Relationships and Filter Context Relationships and Row context Exceptions and Overrides! Iterator Functions Create Row Context During Measure Calculation CALCULATE Creates Filter Context in Calc Columns We can use CALCULATE to “follow” relationships in calc columns Using Measures Within a Row Context: a Genuine Curveball Putting It All Together: Review Example Why Did Our Original Measure Work to Begin With? Recap Within the Context of FILTER() In Case of Emergency… Key Points from This Chapter 24 - CALCULATE and FILTER – More Nuances CALCULATE Filter Arguments Override Pivot Filters The “Secret” Second Purpose of ALL(), FILTER(), Etc. CALCULATE’s Definition Gives Us a Hint! ALL() Is the “Remove Filters” Function, but it’s Also a Table! There Are Dozens of These Dual-Purpose Functions! Could Have Been Separate Functions? Nesting Table Functions Inside One Another FILTER Can Unfilter? Putting it All Together 25 - Time Intelligence with Custom Calendars: Greatest Formula in the World Perhaps Our Favorite Thing in DAX Custom Calendars The Periods Table - a “4/4/5” Example How This Changes Things: We Need to “Write” Our Own Time Intelligence Functions Connecting the Periods Table Simple “Sales in Period” Measure Another Familiar Concept: Sales per Day First New Concept: Sales per Day in Prior Period Getting Organized First Desired Results The Greatest Formula in the World “Clear Filters Then Re-Filter” – Another Name for GFITW Clear Filter Re-Filter: Navigation Arithmetic Table[Column] Uses Row Context Generated by FILTER MAX() Operates Over a Filter Context In Your Periods Table, You Always Need a Numeric PeriodID Column or Equivalent More GFITW measures – Year Over Year and Year To Date Prior Period Sales Year Over Year (YOY) Custom Calendar Measure Year To Date (YTD) Measure with Custom Calendar Fixing Measures to Work at Total Level Suppressing Prior Period for Totals Fixing YOY to Work at Total Level The Fix Fixing Prior Period to Work on Totals, Too The Usual “Percent Growth” Formulas 26 - Advanced Calculated Columns Perspective: Calculated Columns Are Not DAX’s Strength! OK, Power Pivot Calc Columns Are a Strength in Some Ways. But More Difficult in Some Cases Start Out With “Not so Advanced” Grouping Columns Unique Columns for Sorting Another Sort by Column Example Now For the Advanced Examples Summing up in a Lookup Table Use of the EARLIER() Function EARLIER() in Action An Even More Advanced Example Calculated Columns are Static Memory and CPU Consumption During Recalculation of Complex Calc Columns 27 - New DAX Functions… and Variables! Amazing Since 2010, and STILL Improving Important Note: Excel 2016+ and Power BI Desktop Only! New Functions – Some Highlights DATEDIFF() MEDIAN() and PERCENTILE PRODUCT() GEOMEAN() and GEOMEANX() Other Corresponding X Functions CONCATENATEX: The Most Interesting Function in the World? ISEMPTY() INTERSECT(), EXCEPT() and UNION() INTERSECT() EXCEPT() UNION() More New Functions DAX Variables Variables Are like a Tape Recorder Variables Offer Three Benefits Benefit One: Cleaner Formulas The VAR Keyword The RETURN Keyword Referencing a Variable Cleaner Formulas (Benefit One) Revisited Benefit Two: Less “Mysterious” Formulas Example 1: Alternative to EARLIER? Example 2: Measure References Inside FILTER (Within a Measure) 28 - “YouTube for Data” – The Importance of a Server Files – Great for Storage, Not Great for Sharing Email Sucks as a Delivery Vehicle for Our Awesome Work! Network Distribution via File Shares? Not much better. Parallels to Video Files, Circa 1998 Parent, Grandparents, and Pictures of Cats YouTube Happens! Importance of Web/Mobile So We Need “YouTube for Data” PowerBI.com Quick Tour Step 1: Upload XLSX/PBIX File to PowerBI.com Step 2: Sharing Your Dashboard Cloud/Server Option Comparison Cloud/Server Sharing Option: Even More Valuable than YouTube PS: Can We Ask You for a Special Favor? A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!) SSAS Tabular Features Power Pivot to SSAS Tabular Connect to SSAS Tabular from Excel Going Further with SSAS Tabular: Visual Studio Key Takeaways A2 - Cube Formulas – the End of GetPivotData() Formulas Reaching into Pivots = The Dark Ages One Click That Will Change Your Life The Data Is Still “Live!” You Can Also Write Them “From Scratch” For Starters, CUBEVALUE() Is All You Really Need Adding a Slicer is easy… Perspective – When to Use, Tradeoffs, Etc. More Information A3 - Some Common Error Messages Addin is “Out of Sync” “Initialization of the Data Source Failed” Other Scary-But-Harmless Errors Perspective A4 - People: The Most Powerful Feature of Power Pivot Index

First 10 Pages Of the book