DAX Queries

DAX Basic queries:

Distinct Values

evaluate
( values('Table'[MonthoftheYear]))
Order
by 'Table'[MonthoftheYear]
　

Count of rows like count(*)

evaluate
(ROW ( "Count" ,COUNTROWS('Table') ))

Count of Distinct users

evaluate
(ROW ( "Distinct Customers", DistinctCount(Table[Userkey])))

Where Clause

evaluate
( filter('Dimsite', [Sitekey] = 1))
Order By Clause

Order by
evaluate
(
'Dimsite'
)
order by [Sitecode]
　
TOP Clause
　
evaluate
(
TOPN(10, 'Dimsite', [Sitecode])
)

Aggregate

evaluate
(
summarize(
'Table',
[MonthoftheYear],
"Total Sales", SUM('Table'[Customercount] )
)
)

RANK Function:

EVALUATE
(
SUMMARIZE
('Table'
,
'Dimsite'[Sitecode]
,
'Table'[MonthoftheYear]
,
"Table", sum('Table'[Customercount])
,
"Rank",RANKX(ALL('Dimsite'[Sitecode]),SUMX(RELATEDTABLE('Table'),[Customercount]))
)
)
order by [Rank]
　
Min and Max
　
evaluate(
row(
"sitekeymin", min('Table'[Sitekey]) ,
"sitekeymax", max('Table'[Sitekey]) )
)
　
Dates and Time

Equal to Getdate()
evaluate
(
ROW ( " Timestamp’" ,NOW() )
)

Convert Function:

evaluate
(
ROW ( "Beginning of time" ,DATEVALUE( "01/01/1900") )
)

Year,Month and Date:

evaluate
(
ROW ( "First Year" , Year(Date(2013,9,15)),
"First Month", Month(Date(2013,9,15)),
"First Day", Day(Date(2013,9,15)) )
)

Previous Month:

evaluate
(
ROW ( "One month previous" ,eDate(now() ,-1) )
)

Replace-Substitute, Stuff-Replace

evaluate
(
ROW ( "Pen Colour", Substitute("The pen is blue","blue","red"),
"Sky Colour", Replace("The pen is blue", 5,3, "Sky") )
)
　
DAX Advanced queries:

--To get the Sum of Customercount by MonthoftheYear
evaluate
(
summarize
(
'Table',
'Table'[MonthoftheYear],
''
"Total ProfileCount",
sum('Table'[Customercount])
)
)
order
by
'Table'[MonthoftheYear]

--To get the ume of Customercount by Yearmoth by status
evaluate
(
summarize
(
'Table',
'Table'[MonthoftheYear],
'DimStatus'[Status],
"Total ProfileCount",
sum('Table'[Customercount])
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status]

To get the ProfileCount based on status and Hostsite

evaluate
(
summarize
(
'Table',
'Table'[MonthoftheYear],
'Dimsite'[SiteDescription],
'DimStatus'[Status],
"Total ProfileCount",
sum('Table'[Profilecount])
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status],
'Dimsite'[SiteDescription]
　

--Profilecount by hostsite using Rollup function
evaluate
(
summarize
(
'Table',
ROLLUP('Table'[MonthoftheYear]),
'Dimsite'[SiteDescription],
'DimStatus'[Status],
"Total ProfileCount",
sum('Table'[Customercount])
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status],
'Dimsite'[SiteDescription]
　
Filtering Data
evaluate
(
Filter
(
summarize
(
'Table',
'Table'[MonthoftheYear],
'Dimsite'[SiteDescription],
'DimStatus'[Status],
"Total ProfileCount",
sum('Table'[Customercount])
),
'Table'[MonthoftheYear]=201308
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status],
'Dimsite'[SiteDescription]

--At times, you might want to add columns to a table without grouping or summarizing that data.
-- One way to do this is to use the addcolumns function, as shown in the following example:
evaluate
(
filter
(
addcolumns
(
summarize
(
'Table',
'Table'[MonthoftheYear],
'Dimsite'[SiteDescription],
'DimStatus'[Status]
),
"Total ProfileCount",
calculate(sum('Table'[Customercount]))
),
'Table'[MonthoftheYear]=201308
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status],
'Dimsite'[SiteDescription]

Distinct Count:

evaluate
(
Filter
(
summarize
(
'Table',
'Table'[MonthoftheYear],
'DimStatus'[Status],
"Distinct Customers",
DistinctCount(Table[Userkey]),
"Total ProfileCount",
sum('Table'[Customercount])
),
'Table'[MonthoftheYear]=201308
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status]
　
This query covers Filter, Rollup, DistinctCount, IF
　
evaluate
(
Filter
(
summarize
(
'Table',
'Table'[MonthoftheYear],
'DimStatus'[Status],
Rollup('Dimsite'[SiteDescription]),
"Distinct Customers",
DistinctCount(Table[Userkey]),
"Total ProfileCount",
sum('Table'[Customercount])
, "Is this a SubTotal?",
if(IsSubtotal('Dimsite'[SiteDescription]), "Yes", "No")
),
'Table'[MonthoftheYear]=201308
)
)
order
by
'Table'[MonthoftheYear],
'DimStatus'[Status]