People.highline.edu



Topics in Video:Standard PivotTable or Data Model PivotTable?Excel Power Pivot & Power BI Desktop?Power Query to Extract, Transform and Load Data to Data ModelUse Power Query to Refine Data ModelBuild RelationshipsIntroduction to Building DAX Formulas: Calculated Columns & MeasuresNumber Formatting For DAX MeasuresData Model PivotTableIntroduction to how DAX Formula are Calculated: Row Context & Filter ContextDAX Studio to help us understand How DAX Formulas are CalculatedExisting Connections to visualize the Table that DAX Formulas “see”Row Area Filters from Fact Table or Dimension Table?Explicit DAX Formulas rather than Implicit DAX FormulasHiding Columns and Tables from Client ToolBuild Date TableIntroduction to Time Intelligence DAX FunctionsTOTALYTDIntroduction to CALCULATE FunctionRunning Total over Multiple YearsExample of DAX Calculation that is easier to make than in a Standard PivotTableAdding New Data and RefreshingChoice between: Standard PivotTable & Data Model PivotTableStandard PivotTable:Have One Flat TableDon’t have Big DataStandard Calculation in PT sufficientMust manually add Number Format for each new CalculationCan NOT re-use a FormulaFor simple PivotTable Reports on a small data set, Standard PivotTables are great.Data Model PivotTable:Have Multiple TablesHave Big DataMore Calculations with DAXNumber Formatting can be added to formulaDAX Measures (Formulas) are created once, and can be re-used many timesFor complex projects or Big Data, Data Model PivotTables are great.I switch from Standard PivotTable over to Data Model PivotTables when these occur:Have more than one tableHave more than about 50,000 rows of dataWant DAX Formulas because:DAX can make more varied calculationsCan have Number Formatting attached to formulaCan use formula over and over.Choice between: Excel Power Pivot & Power BI DesktopExcel Power Pivot:Power Query, Columnar Database, Relationships, DAX Formulas are almost identical in both.PivotTable Report is what you wantHave Excel Worksheets to compliment Data Model PivotTable Reports that allow you freedom to:Work in cells, not columns and tablesHave any of the other Excel features to compliment Data Model PivotTablesFamiliar with Excel.DAX Formula calculate more slowly in Excel because they are calculated with MDX, which uses only one processor at a time.Hard to share Power Pivot Report.Power BI Desktop:Power Query, Columnar Database, Relationships, DAX Formulas are almost identical in both.More varied Visualizations and ReportsVisualizations and Reports are interactive (one can filter the other)You can publish Visualizations and Reports, so they can be consumed on any device.Table DAX Formulas can be part of the Data Model as a Table.DAX Formulas calculate more quickly in Power BI because they are calculated using DAX which allows parallel processors to work on calculations. This matters for big data.I switch from Excel Power Pivot to Power BI Desktop when:Want the Visualizations in Power BI, rather than a PivotTableWant interactivity between visualizations and ReportsWant to publish the Visualizations and ReportsI have too much data and I need faster calculation times in Power BITopics in Video:Power Query to Extract, Transform and Load Data to Data ModelImport CSV Data From Folder into Data ModelImport Access Database Data into Data ModelImport Excel Table into Data ModelUse Power Query to Refine Data ModelConverting ISO DataConvert Snow Flake Model to Star Scheme ModelRound Numbers with Many DecimalsBuild RelationshipsIntroduction to Building DAX Formulas: Calculated Columns & MeasuresCalculated ColumnsCalculate RevenueRow Context in a table = even though we use column references in our formula, the formula sees each row in the table and can pull out the correct number for each row.RELATED rather than VLOOKUPROUND is the same as in Excel.Final Calculated Column formula is:=ROUND(fTransactions[UnitsSold]*(1-fTransactions[Discount])*RELATED(dProduct[RetailPrice]),2)MeasuresSUMX DAX Function for calculating Revenue.The SUMX Function simulates what we do in the two-step process of 1) building a Calculated Column to calculate the Revenue for each row in the Table, and then 2) using the SUM Function in a Measure to add the values from the Calculated Column.The SUMX Function is an iterator function, where iterator just means that the SUMX can iterate over a table and calculate a value for each row in the table (using Row Context), and then the SUMX takes the calculated values and SUMS them to get a single total.You can think of the SUMX Function as a super-charged Array Formula that can create the full helper column in a single cell and then add the result.SUMX is just one of many “Iterative DAX Functions”, that can create a Row Content for a table, calculate a value for each row, and the make an aggregate calculation.There are other “X” Iterative functions like that iterate and then aggregate:AVERAGEXRANKXMINXSUMXAnd more…There are other Iterative functions that do not have an “X” in the name like and do not aggregate:FILTERADDCOLUMNSAnd more…When you drag a Measure to the Values Area of a Data Model PivotTable, the Measure sees the “Filter Context”.“Filter Context” simply means that the Measure can “see” all the criteria/conditions/filters from the PivotTable Row Area, Column Area, Filter Area and Slicer Area.When a Measure is dropped into the Values Area of a PivotTable that has the Product Name from a dimension table dropped in the Row Area, the Columnar Database and the Power Pivot Engine will filter the dProduct table down to a single row, and in turn this filter flows across the relationship and filters the Fact Table down to just the records for the given product, in this way the Fact Table is made much smaller before the DAX Formula has to make the calculations.Convention for DAX Formulas:When you refer to a column in a table, always use the table name and then in square brackets the field name.Example: to refer to the Retail Price Field in the dProduct table, use: dProduct[RetailPrice]When you use a Measure in a Formula use the name of the Measure in square brackets.Example: to use the Total Revenue measure, use: [Total Revenue]Why? So we know when we see a Measure in a formula we know that a hidden CALCULATE Function is present, and may have an impact on whether or not Context Transition occurs (more later).Final SUMX Measure:Total Sales:=SUMX(fTransactions,ROUND(fTransactions[UnitsSold]*(1-fTransactions[Discount])*RELATED(dProduct[RetailPrice]),2))Number Formatting For DAX MeasuresData Model PivotTableIntroduction to how DAX Formula are Calculated: Row Context & Filter ContextRow ContextFilter ContextRow Area Filters from Fact Table or Dimension Table?Explicit DAX Formulas rather than Implicit DAX FormulasImplicit DAX MeasuresImplicit = Drag and drop field into Data Model PivotTable/Power BI Values Area and the Data Model makes a Read Only Measure for youMUCH Less Control when you use ImplicitNumber Field will use SUM FunctionText Field uses COUNTYou can't add Number Formatting to the MeasureYou can't Reuse FormulaYou can't change the name of the MeasureIf you right-click an Implicit Measure in the Values Area of the PivotTable you can change the "Summarize Values By", but then it adds yet another Implicit MeasureExplicit DAX MeasureExplicit = You create the formula, choose the name, add the Number Formatting and use it over and over.You create formula in Measured Grid, or Measure dialog boxMUCH More Control when you use ExplicitYou choose what functions go into your MeasureYou can name the MeasureYou can apply Number Formatting that will follow the Measure around when it is reused.You can use the formula over and over.You won't have a bunch of extra Measures created by right-click, "Summarize Values By".Hiding Columns and Tables from Client ToolThis makes the Reporting side easier for the user.Editing Power Query Code to change Data Model.Go back and remove Supplier ID and Category IDBuild Date TableAutomatic Data Table (Calendar Table) in Excel Power Pivot:Click in data column of Fact TableIn the Design Ribbon Tab, in the Calendar group, click Date Table drop-down, then click NewExtend Data Table with Calculated ColumnsSort by Column to get Months to Sort CorrectlyWhy you do NOT want to use the “Automatic Grouping Feature” in a Data Model.Because the automatic Grouping will add Calculated Columns to a Fact Table (may add a lot of data to the size of the stored Columnar database.If you do not have a Date Table, you can not use Data Intelligence Functions like:TOTALTYDData Date must have these characteristics:All the days in each yearIntroduction to Time Intelligence DAX FunctionsWe looked at TOTALYTDTime Intelligence Functions depend on having a Proper Date Table.Formula Used:For multiple Years:Running Total CALC:=CALCULATE([Total Sales],FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))orFor within a Year:Running Total:=TOTALYTD([Total Sales],'Calendar'[Date]) ????????????Introduction to CALCULATE FunctionExample of DAX Calculation that is easier to make than in a Standard PivotTableAdding New Data and RefreshingPicture of Excel Dashboard:Picture of Data Model:Picture of Power BI Published Report:Picture of Power BI Data Model: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download