DAX queries in Power BI

The acronym DAX stands for Data Analysis Expressions. DAX is a set of functions, operators, and constants which can be used to construct formulas from existing information in your data store. if you are familiar with creating functions in Excel that knowledge comes in handy to create DAX functions.

An example for DAX function format would be like:

<MEASURE_NAME> = <DAX FUNCTION>(<TABLE_NAME>[<COLUMN_NAME>])
Eg:
Total sales = SUM(financials[sales])

Lets check out some of the DAX functions

DISTINCT
Distinct Cities = DISTINCT(Portfolio[City])

SUM
Total Sales = SUM(financials[sales])

SUMX
Allows to get the sum of an expression evaluated for each row in the table.
SUMX(<table>, <expression>)

FILTER
Filtered sales = FILTER(financials, financials[ Sales] > 100000)
Here you can create a new table to filter and get rows where sales are greater than 100000. The base table is 'financials'. The new table created is 'Filtered sales'.

CALCULATE
For this example you can refer to a sample table which I created. Refer the code in Annex for table creation.

total online sales = CALCULATE(SUM('Sales Types'[Amount]),FILTER('Sales Types','Sales Types'[Mode]="ONLINE"))

online sales 2010 = CALCULATE(SUM('Sales Types'[Amount]),FILTER('Sales Types','Sales Types'[Mode]="ONLINE"), 'Sales Types'[Year]="2010")

The total online sales put into a table would look like below

SUMMARIZE

Sales Summary = SUMMARIZE('Sales Types', 'Sales Types'[Region], "Sales per region", COUNT('Sales Types'[Amount]), "online sales count", CALCULATE(COUNT('Sales Types'[Mode]), FILTER('Sales Types','Sales Types'[Mode]="ONLINE")), "total online sales", 'Sales Types'[total online sales], "total store sales", 'Sales Types'[total store sales], "store sales count", CALCULATE(COUNT('Sales Types'[Mode]), FILTER('Sales Types','Sales Types'[Mode]="STORE")))

UNION
Union Test = UNION(SELECTCOLUMNS(Employees, "Lat", Employees[Lat], "Lon", Employees[Lon], "Type", "Employee"), SELECTCOLUMNS(Portfolio, "Lat", Portfolio[LAT], "Lon", Portfolio[LONG], "Type", "Office"))

In the last example above, I have used the UNION and SELECTCOLUMN function to get both office and employee locations in a hypothetical organization.

In order to visualize this sample you can create a new table and provide the function as shown below

Annex

Sales Figures =
DATATABLE (
    "Year", STRING,
    "Asia", DOUBLE,
    "Europe", DOUBLE, "Australia", DOUBLE,
    "America", DOUBLE,
    {
        { "2010", 100000, 200000,80000, 100000 },
        { "2011", 400000, 250000,100000, 100000 },
        { "2012", 600000, 350000,150000, 100000 }
    }
)

Sales Types =
DATATABLE (
    "Year", STRING,
    "Region", STRING,
    "Amount", DOUBLE,
    "Mode", STRING,
    {
        { "2010", "Asia", 80000, "STORE"}, { "2010", "Asia", 25000, "ONLINE"}, { "2010", "Europe", 180000, "STORE"}, { "2010", "Europe", 125000, "ONLINE"}, { "2010", "Australia", 180000, "STORE"}, { "2010", "Australia", 125000, "ONLINE"},
        { "2011", "Asia", 80400, "STORE"}, { "2011", "Asia", 35000, "ONLINE"}, { "2011", "Europe", 185000, "STORE"}, { "2011", "Europe", 145000, "ONLINE"}, { "2011", "Australia", 175000, "STORE"}, { "2011", "Australia", 227500, "ONLINE"},
{ "2012", "Asia", 90000, "STORE"}, { "2012", "Asia", 45000, "ONLINE"}, { "2012", "Europe", 190500, "STORE"}, { "2012", "Europe", 115000, "ONLINE"}, { "2012", "Australia", 180000, "STORE"}, { "2012", "Australia", 145000, "ONLINE"},
{ "2013", "Asia", 94500, "STORE"}, { "2013", "Asia", 15000, "ONLINE"}, { "2013", "Europe", 194000, "STORE"}, { "2013", "Europe", 95000, "ONLINE"}, { "2013", "Australia", 200000, "STORE"}, { "2013", "Australia", 195000, "ONLINE"},
{ "2014", "Asia", 110000, "STORE"}, { "2014", "Asia", 225000, "ONLINE"}, { "2014", "Europe", 185000, "STORE"}, { "2014", "Europe", 120000, "ONLINE"}, { "2014", "Australia", 205000, "STORE"}, { "2014", "Australia", 225000, "ONLINE"}
    }
)