Sample DAX Queries

Following are the simple DAX Queries on Adventure Work DB.

/*Display the number of records in a table
EVALUATE
ROW("TotalRows", COUNTROWS(DimCustomer))
*/

/* using Measures
DEFINE
MEASURE 'InternetSales'[Internet Total Sales] = SUM([SalesAmount])
    MEASURE 'InternetSales'[Internet Total Product Cost] = SUM([TotalProductCost])
    MEASURE 'InternetSales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]
EVALUATE
SUMMARIZE(
    'InternetSales',
    'DimDate'[CalendarYear],
    "Sales", 'InternetSales'[Internet Total Sales],
    "Cost", 'InternetSales'[Internet Total Product Cost],
    "Margin", 'InternetSales'[Internet Total Margin]
)
*/


/* Display FirstName starting with B
DEFINE MEASURE 'DimCustomer'[FullName] = VALUES('DimCustomer'[FirstName])&Values('DimCustomer'[LastName])
EVALUATE
FILTER(
SUMMARIZE(
'DimCustomer'
,[FirstName]
,[LastName]
)
,LEFT('DimCustomer'[FullName],1)="B")
*/


/* Display SalesAmount which has MAX TaxAmount
DEFINE MEASURE 'InternetSales'[MaxTax] = MAX('InternetSales'[TaxAmt])
EVALUATE
FILTER(
SUMMARIZE(
'InternetSales'
,[SalesAmount]
,"MaxTax"
,MAX('InternetSales'[TaxAmt])
)
,MAX('InternetSales'[TaxAmt])='InternetSales'[MaxTax]
)
*/

/* Display customers who bought 1 or 2 or 3
EVALUATE
FILTER(
SUMMARIZE(
'InternetSales'
,'DimCustomer'[FirstName]
,'DimCustomer'[LastName]
,'DimProduct'[EnglishProductName]
,'DimDate'[CalendarYear]
)
,'DimProduct'[EnglishProductName] = "HL Mountain Tire" ||
'DimProduct'[EnglishProductName] = "ML Road Tire" ||
'DimProduct'[EnglishProductName] = "Road Tire Tube"
)
*/

/*Display Productname and its class, if class is null then display it as "NA"
EVALUATE
SUMMARIZE(
'DimProduct'
,'DimProduct'[EnglishProductName]
,"Class"
,IF(ISBLANK(Values('DimProduct'[Class])),"NA",Values('DimProduct'[Class]))
)
*/


/*avg sales for currencies
EVALUATE
SUMMARIZE(
'InternetSales'
,DimCurrency[CurrencyName]
,"AvgSales"
,AVERAGE('InternetSales'[SalesAmount])
)
*/

/*Display the FirstName and LastName of records from the Contact table
where FirstName contains the letters 'ss'.
Display an additional column with sequential numbers for each row returned
beginning at integer 1
DEFINE MEASURE 'DimCustomer'[FullName] = VALUES('DimCustomer'[FirstName])&Values('DimCustomer'[LastName])
EVALUATE
ADDColumns(
FILTER(
SUMMARIZE(
'DimCustomer'
,[FirstName]
,[LastName]
)
,SEARCH("ll",'DimCustomer'[FirstName])>0)
,"ID"
,RANKX(All('DimCustomer'[FirstName]),'DimCustomer'[FullName]))
*/

/*Display the [SalesPersonID] with an additional column entitled 'Band'
indicating the appropriate band as above
Evaluate
SUMMARIZE(
'InternetSales'
,'DimProduct'[EnglishProductName]
,'InternetSales'[SalesAmount]
,"Band"
,IF('InternetSales'[SalesAmount] <=500 , "Band1", If(500 < 'InternetSales'[SalesAmount] && 'InternetSales'[SalesAmount]<=1000,"Band2","Band3"))
)
*/

/*single vlaued Parameters

DEFINE
MEASURE 'InternetSales'[Internet Total Sales] = SUM([SalesAmount])
    MEASURE 'InternetSales'[Internet Total Product Cost] = SUM([TotalProductCost])
    MEASURE 'InternetSales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]
EVALUATE
Filter(
SUMMARIZE(
    'InternetSales',
'DimDate'[CalendarYear],
    "Sales", 'InternetSales'[Internet Total Sales],
    "Cost", 'InternetSales'[Internet Total Product Cost],
    "Margin", 'InternetSales'[Internet Total Margin]
)
,'DimDate'[CalendarYear]=Value(@Year)) //Year as string type parameter in report

*/

/* Multi Valued Parameters
DEFINE
MEASURE 'InternetSales'[Internet Total Sales] = SUM([SalesAmount])
    MEASURE 'InternetSales'[Internet Total Product Cost] = SUM([TotalProductCost])
    MEASURE 'InternetSales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]
evaluate
Calculatetable(
summarize(
    'InternetSales'
, DimDate[CalendarYear],
"Sales", If(ISBLANK('InternetSales'[Internet Total Sales]),Blank(),'InternetSales'[Internet Total Sales]),
    "Cost", IF(ISBLANK('InternetSales'[Internet Total Product Cost]),Blank(),'InternetSales'[Internet Total Product Cost]),
    "Margin", IF(ISBLANK('InternetSales'[Internet Total Margin] ),Blank(),'InternetSales'[Internet Total Margin])
)
, filter(
values(DimDate[CalendarYear])
, pathcontains(
substitute(
substitute(
substitute( @Year, "{ ", "")
, " }", "")
, ",", "|")
, DimDate[CalendarYear])
)
)
*/

/* AlphaNumeric parameters
Evaluate
Filter(
Summarize(
'DimCustomer'
,[FirstName]
,[LastName]
,[AddressLine1]
)
,[AddressLine1]=@Address)
*/


/* Image
Evaluate
Summarize(
'DimProduct'
,[EnglishProductName]
,[LargePhoto]
)
*/