Home > DAX > DAX – Queries

DAX – Queries

Recently, I am completely focusing on Tabular and DAX to learn new concepts in SQL 2012. Personally I can feel it is a mixed concept of writing TSQL and MDX though it is based on tables.

I am excited to start my journey with DAX and planning to write a series of posts. Here discussing few very basic queries to start with DAX

1. Select all columns from Internet Sales tables

EVALUATE
(
'Internet Sales'
)



2. Select columns from Internet Sales table

EVALUATE
summarize(
'Internet Sales',
'Internet Sales'[Order Date],
'Internet Sales'[Ship Date],
'Internet Sales'[Unit Price]
)



3. Filter the table records

EVALUATE ( 
filter (
'Internet Sales'
, 'Internet Sales'[Unit Price] > 3500
)
)



4. Filter the record set

EVALUATE
( 
filter (
summarize( 'Internet Sales',
'Internet Sales'[Order Date],
'Internet Sales'[Ship Date],
'Internet Sales'[Unit Price]
)
, 'Internet Sales'[Unit Price] > 3500
)
)



5. Filter the record set and order the result set

EVALUATE
( 
filter (
summarize(
'Internet Sales',
'Internet Sales'[Order Date],
'Internet Sales'[Ship Date],
'Internet Sales'[Unit Price]
)
, 'Internet Sales'[Unit Price] > 3500
)
)
ORDER BY 'Internet Sales'[Order Date] DESC



6. Record Count of tables

EVALUATE
row( "Product TableCount",
countrows('Product'),
"Internet Sales TableCount",
countrows('Internet Sales')
)



7. Cross join of two tables

EVALUATE
crossjoin (
'Product',
'Internet Sales'
)



8. Cross join of values (columns) of tables

EVALUATE (
summarize(
crossjoin ( 
//values('Product'[Product Name]),
values('Product Category'[Product Category Name]),
values('Internet Sales'[Unit Price]) 
) 
// ,'Product'[Product Name]
,'Product Category'[Product Category Name] 
,'Internet Sales'[Unit Price] 
)
)



9. Get the analysis of Internet sales using other tables

EVALUATE (
summarize( 'Internet Sales' 
,'Product Category'[Product Category Name]
,"SumofInternetSalesUnitPrice" 
,SUM('Internet Sales'[Unit Price]) 
)
)



10. Format of the results

EVALUATE (
summarize( 'Internet Sales' 
,'Product Category'[Product Category Name]
,"SumofInternetSalesUnitPrice" 
,FORMAT(SUM('Internet Sales'[Unit Price]),"currency")
)
)



11. Result set using Date table

EVALUATE (
summarize( 'Internet Sales' 
,'Date'[Calendar Year] 
,'Product Category'[Product Category Name] 
,"SumofInternetSalesUnitPrice" 
,FORMAT(SUM('Internet Sales'[Unit Price]),"currency") 
)
)



12. Filter the result set to year value

EVALUATE ( 
filter(
summarize( 'Internet Sales' 
,'Date'[Calendar Year] 
,'Product Category'[Product Category Name] 
,"SumofInternetSalesUnitPrice" 
,FORMAT(SUM('Internet Sales'[Unit Price]),"currency") 
)
, 'Date'[Calendar Year] = 2007
)
)



Points to consider:

1. Most of the queries are self-explanatory with results so haven’t provided description
2. Planning to continue this series with additional DAX functions

Advertisements
Categories: DAX Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: