DAX – query language for Power BI and Power Pivot
DAX (Data Analysis Expression) is a language used in Power Pivot, Power BI and SQL Analysis Services.
It is similar to Excel formulas, but different in many ways.
Three ways of DAX use in Power Pivot or Power BI
New column creation
- Creation of a new column, which is based on other columns. The formula looks like this:
=[Revenue]*0,5
Measure creation
- Creation of measure, like sum or average of whole column. In this case the data are typed below the table in Power Pivot or in New Measure in Power BI.
The formula is like this:
=SUM([Revenue])
New table creation
You can use DAX function to create a new table, which works like a view in database – does not contain any data, just presents data from other tables.
The formula is like this:
List of items = DISTINCT(‘original data'[Goods])
Click here for more info.
- % of parent row in Power BI
- ALLEXCEPT – removes all filter contexts except one (DAX – Power Pivot, Power BI)
- Calculation Groups – easy calculations with multiple measures at the same time
- CALENDARAUTO – create the time dimension automatically (DAX – Power Pivot, Power BI)
- CONCATENATEX (DAX – Power Pivot, Power BI)
- CONTAINSSTRING, CONTAINSSTRINGEXACT – finds text string in another text string (DAX – Power Pivot, Power BI)
- CROSSJOIN – all combinations of two lists (DAX – Power Pivot, Power BI)
- DATATABLE – create your data directly in Data Model with no connection (DAX – Power Pivot, Power BI)
- DATEADD – values for some period shifted in time by a year, quarter or day (DAX – Power Pivot, Power BI)
- DATEDIFF – difference between two dates or times in units you need
- DATESMTD, DATESQTD, DATESYTD – dates from the beginning of month / quarter / year (DAX – Power Pivot, Power BI)
- DAX – comparison of a specific row with all rows of the table
- DISTINCT (DAX – Power Pivot, Power BI)
- DIVIDE – division handling zero (DAX – Power Pivot, Power BI)
- EXCEPT – comparison of tables – finding of additional rows (DAX – Power Pivot, Power BI)
- FILTER vs KEEPFILTERS vs VALUES – short comparison
- Fundamental difference between DATEADD a PARALLELPERIOD functions (DAX – Power Pivot, Power BI)
- GROUPBY – aggregations in data model using DAX (DAX – Power Pivot, Power BI)
- How not to spoil your Power BI data model – 7 critical rules
- IN – operator recognizing presence in a list of values (DAX – Power Pivot, Power BI)
- INTERSECT – finding of common rows in two tables (DAX – Power Pivot, Power BI)
- ISFILTERED and removing of subtotals in visualizations
- ISFILTERED vs ISINSCOPE – basic difference
- LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
- MIN, MAX – highest and smallest values in range (DAX – Power Pivot and Power BI)
- NAMEOF -function to get name of column (DAX – Power Pivot, Power BI)
- NATURALINNERJOIN – creates new table from common rows of two tables
- NATURALLEFTOUTERJOIN – new table using left outer join (DAX – Power Pivot, Power BI)
- NETWORKDAYS – number of workdays between two dates (DAX – Power Pivot, Power BI)
- NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR, PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR – values from previous or next time period (DAX – Power Pivot, Power BI)
- Number of unique rows – by multiple columns
- OFFSET – value from previous, next… row in visual (DAX – Power Pivot, Power BI)
- PARALLELPERIOD – travelling in time (DAX – Power Pivot, Power BI)
- PATH, PATHLENGTH, PATHITEM, PATHITEMREVERSE, PATHCONTAINS – playing whith hierarchies (DAX – Power Pivot, Power BI)
- Pearson correlation coefficient in Power BI
- Playing with SAMEPERIODLASTYEAR and TOTALYTD functions
- RANK.EQ – determine the order of value in some column (DAX – Power Pivot, Power BI)
- RELATEDTABLE – assigning of table connected by relation (DAX – Power Pivot, Power BI)
- ROUND, ROUNDUP, ROUNDDOWN, MROUND – rounding functions (DAX – Power Pivot, Power BI)
- Running totals simply (DAX – Power Pivot, Power BI)
- SAMEPERIODLASTYEAR – comparison with previous year (DAX – Power Pivot, Power BI)
- SELECTCOLUMNS – select some columns from table (DAX – Power Pivot, Power BI)
- SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI)
- SUMMARIZECOLUMNS – grouping in data model (DAX – Power Pivot, Power BI)
- SUMX vs SUM – key differences very briefly (DAX – Power Pivot, Power BI)
- SWITCH for simple formulas with multiple conditions
- TOPN – first N rows from table (DAX – Power Pivot, Power BI)
- TOTALYTD, TOTALQTD, TOTALMTD – cumulative subtotals in time (DAX – PowerPivot, Power BI)
- TREATAS – simple calculation using “non existent” relations (DAX – Power Pivot, Power BI)
- UNION – appending of multiple tables in DAX (DAX – Power Pivot, Power BI)
- Use of SAMEPERIODLASTYEAR (or similar DAX functions) together with filters and slicers
- USERPRINCIPALNAME – show users name and use it in RLS (DAX – Power Pivot, Power BI)
- Variables in DAX
- Visuals title, dynamically changed by slicer selection
- CALCULATE – “merging” of other functions (DAX – Power Pivot, Power BI)
- FILTER – filters in DAX (DAX – Power Pivot, Power BI)
- IF – simple condition (DAX – Power Pivot, Power BI)
- RELATED (DAX – Power Pivot, Power BI)
- SUMX – summary with filter or additional calculation (DAX – Power Pivot, Power BI)
