sqlgene wwwsqlgenecompowerbi emeidingeralllinestechcom About me Business Intelligence developer Worked for AllLines for 5 years Spoken at Pittsburgh SQL User Group and various SQL Saturdays ID: 1028991
Download Presentation The PPT/PDF document "Eugene Meidinger An (Advanced) Introduct..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
1. Eugene MeidingerAn (Advanced) Introduction to DAX@sqlgenewww.sqlgene.com/powerbi/emeidinger@all-lines-tech.com
2. About meBusiness Intelligence developerWorked for All-Lines for 5 yearsSpoken at Pittsburgh SQL User Group and various SQL SaturdaysHelp lead the Pittsburgh Power BI User GroupPluralsight AuthorWent from SQL newb to SQL pro
3. What is the goal of this talk?Focus on core conceptsBuilding the basic mental modelWhat is difficult to understand?
4. OverviewCalculated columns vs measuresColumnar storageAggregationsFilteringFilter contextsIterators
5. DAX is NOT XDAX is not ExcelDAX is not SQLDAX is not MDXDAX is painful if you don’t get thisThe concepts are harder than the syntax
6. There are two types of business logicCalculated columnsMeasures
7. Calculated columnsRepresents a single value per rowComputed at time of creation/refreshResults are materialized and stored with the tableAttached to a specific tableNormally can only see the row they exist inRelates to the idea of row contextMore on this laterCan be used in filters or values/results areas
8. Example Calculated ColumnGrossMargin = Sales[Price] – Sales[Cost]
9. MeasuresRepresents a single value per data modelComputed at run timeResults are dynamic, based on filtersThis is called the filter contextNot attached to any tableSees all the data at once
10. Example MeasureTotalQuantity := SUM(Sales[Quantity])
11. Implicit measuresIf you use a calculated column as a value/result it creates an implicit measure
12. DAX is good at two thingsAggregationsFiltering
13. What is an aggregation?Aggregation is combining a group of values into one valueExamplesSUMAVERAGEMINMAXDISTINCTCOUNT
14. Quantity4132751SUM(Quantity)23
15. DAX stores data as columnsSuper efficient for simple aggregationsMany aggregate functions take columns as parametersDAX is optimized for single-column operationsColumns are the fundamental unit of measure
16. Basic units of measureColumnTableA set of columns with the same length and sort orderScalar ValueRowA table filtered down to a single rowAlso called a row context
17. Compression and encodingValue EncodingDictionary EncodingRun-length encodingSorting
18. ColorBlueGreenGreenRedRedRedRedColorBlue,1Green,2Red, 4
19. DAX is good at two thingsAggregationsFiltering
20. Two types of FilteringImplicit filteringExplicit filtering
21. Implicit FilteringSlicersCell Location
22. Explicit FilteringGreenQuantity := CALCULATE(SUM(Sales[Quantity]), Sales[Color]="Green")Explicit filtering supersedes implicit filtering
23. Filtering ContextA combination of all the user filtersBasic filters are associated with specific, individual columnsCALCULATE allows you to overwrite the filter context
24. Removing FilteringThe ALL() function removes filtersCan be used on a whole table, or specific columnsAllColors:=CALCULATE(SUM(Sales[Quantity]), ALL(Sales[Color]))
25. Applying complex filteringThis causes an error:CALCULATE(SUM(Sales[Quantity]), Sales[Price] - Sales[Cost] <= 1)Need to use something called an “iterator”FILTER() takes in a table and an expressionReturns a filtered table
26. LowMargin:=CALCULATE(SUM(Sales[Quantity]),FILTER(Sales, Sales[Price] - Sales[Cost] <= 1))Example FILTER()
27. IteratorsProcess tables row by agonizing rowExpensive to processRows are filtered one at a time.Called a row contextOften a filter context AND a row context is appliedNecessary to refer to multiple fields in the same rowSUM(Sales[Price] - Sales[Cost]) raises an error
28. Example IteratorAverageGrossMargin1:=AVERAGE(Sales[GrossMargin])AverageGrossMargin2:=AVERAGEX(Sales, Sales[Price] - Sales[Cost])Requires a table parameter to “iterate” throughIterators are expensive
29. Key conceptsCalculated columns are materialized values in a tableMeasures look at all the data plus the filter contextExplicit filtering overrides implicit filteringDAX is optimized for single-column operationsRows don’t exist, but row contexts doMulti-column operations require iterators, which are expensiveCALCULATE() + FILTER() / ALL() can be used to apply advanced filtering
30. Book Recommendations
31. Questions?Contact Info@sqlgenewww.sqlgene.com/powerbi/emeidinger@all-lines-tech.com
32.