DAx Functions
Uploaded by
ScribdTranslationsDAx Functions
Uploaded by
ScribdTranslationsShortcuts
Alt + Enter or Shift + Enter = Move to a new line
Tab = Indentation or code autocompletion (intellisense)
Add Comments
Alt + ↑↓ = Move a line
Shift + Alt + ↑↓ = Copy a line
Alt + Click = Multiple selections
Ctrl + Scroll Mouse = Zoom DAX expression
Click in parentheses = Highlights where the function ends
Ctrl + F2 or Ctrl + Shift + L = Change the same table/column/measure multiple times.
Shift + ? = Power BI Desktop keyboard shortcuts list
Reference
The provided link does not contain translatable text.
Columns
Module: Date and Time Functions
Year = YEAR(DimCalendar[DateKey])
Month = MONTH(DimCalendar[DateKey])
Day = DAY(DimCalendar[DateKey])
Quarter = QUARTER(DimCalendar[DateKey])
WeekYear = WEEKNUM(DimCalendar[DateKey],2)
Day of the week = WEEKDAY(DimCalendar[DateKey],2)
DATEDIFF(DimPromotion[StartDate], DimPromotion[EndDate], DAY)
Module: Conditional and Logical Functions
FactSales[SalesQuantity] - FactSales[ReturnQuantity]
Return = IF(FactSales[ReturnQuantity]>0,"Yes","No")
IF(FactSales[SalesQuantity]>10,"Wholesale","Retail")
IF(FactSales[ReturnQuantity]=0,BLANK(),IF(FactSales[ReturnQuantity]=1,"Unique","Multiple"))
Weekend
Bad Luck = IF((DimCalendar[CalendarDayOfWeekLabel]="Tuesday" || DimCalendar[Calendar
Wholesale Discount
Module: Text Functions
Full Location = DimGeography[RegionCountryName] & ", " & DimGeography[ContinentName]
Quarter = "Quarter " & QUARTER(DimCalendar[DateKey])
LEFT(DimCalendar[CalendarMonthLabel],1)
My Txt = FORMAT(DimCalendar[DateKey], "MMMM")
Day Txt = FORMAT(DimCalendar[DateKey], "DDDD")
FS = UPPER(DimCalendar[Fin Semana])
FS = LOWER(DimCalendar[End of Week])
SUBSTITUTE(DimStores[StoreName], "Store", BLANK())
Module: RELATED Function
Physical Location = IF(DimStores[StoreType]="Store", DimStores[City] & ", " & RELATED(DimGeography[Location]
Unit Price = RELATED(DimProduct[UnitPrice])
Revenue = FactSales[Unit Price] * FactSales[Total Quantity]
Measures
Module: Mathematical and Statistical Functions
Sales Quantity = SUM(FactSales[SalesQuantity])
Return Quantity
[Sales Quantity] - [Returns Quantity]
Return Ratio = DIVIDE([Return Quantity], [Sales Quantity], 0)
The amount of returns relative to sales is:
Average Price = AVERAGE(DimProduct[UnitPrice])
Number of Stores = COUNT(DimStores[StoreKey])
Number of Stores = COUNTA(DimStores[StoreKey])
Number of Stores = COUNTROWS(DimStores)
Number of Stores with Sales
Number of Regions = COUNTA(DimGeography[RegionCountryName])
Number of Regions 2 = COUNTROWS(DimGeography)
Number of Blank Regions = COUNTBLANK(DimGeography[RegionCountryName])
Unique Regions Count = DISTINCTCOUNT(DimGeography[RegionCountryName])
COUNTROWS(FactSales)
Module: CALCULATE Function
CALCULATE([Total Orders], FactSales[ReturnQuantity] > 0)
CALCULATE([Total Orders], FactSales[ReturnQuantity] > 0, FactSales[Type
Module: ALL / FILTER Function
ALL Total Orders = CALCULATE([Total Orders], ALL(FactSales))
% Returned Orders = DIVIDE([Total Returned Orders], [ALL Total Orders])
Average Unit Price = CALCULATE([Average Unit Price], ALL(DimProduct))
Total Returned Orders Multiple FILTER = CALCULATE([Total Orders], FILTER(FactSales, FactSales[ReturnQuan
Total High PU Orders = CALCULATE([Total Orders], FILTER(DimProduct, DimProduct[UnitPrice] > [Average PU G
Module: SUMX Function
SUMX(FactSales, FactSales[TotalQuantity] * RELATED(DimProduct[UnitPrice]))
Module: Time Intelligence Functions
YTD Revenue = CALCULATE([Total Revenue], DATESYTD(DimCalendar[DateKey]))
TOTALYTD([Total Revenues], DimCalendar[DateKey])
MTD Revenues = TOTALMTD([Total Revenues],DimCalendar[DateKey])
Module: Variation Functions
CALCULATE([Total Revenues], SAMEPERIODLASTYEAR(DimCalendar[DateKey]))
[Total Revenue] - [Total Revenue LY]
CALCULATE([Total Revenues], DATEADD(DimCalendar[DateKey],-1,MONTH))
Module: Modifying Measurements
IF(FactSales[SaleType] = "Wholesale" && FactSales[channelKey] = 1 && FactSales[PromotionKey] = 1,
SUMX(FactSales, FactSales[TotalAmount] * RELATED(DimProduct[UnitPrice]) * (1 - FactSales[
Total Income is the number of pieces multiplied by the unit price and by the applied discount.
Total Income Without Discount = SUMX(FactSales, FactSales[TotalQuantity] * RELATED(DimProduct[UnitPrice]))
Module: RANKX Function
Rank Store Revenues = IF( ISBLANK([Total Revenues]), BLANK(), RANKX(ALL(DimStores), [Total Revenues]))
Rank Selected Store Incomes = IF( ISBLANK([Total Income]), BLANK(), RANKX(ALLSELECTED(DimStores),
Rank Revenues SubProductCategories = IF( ISBLANK([Total Revenues]), BLANK(), RANKX(ALL(DimProductSubcategory), [
Rank Income SubCategories Selected Products = IF( ISBLANK([Total Income]), BLANK(), RANKX(ALLSELECTED(Dim
Module: SWITCH Function
Category Size Store = SWITCH( TRUE(),
Small
DimStores[SellingAreaSize] <= 800, "Median",
If DimStores[SellingAreaSize] > 800, then 'Large'
Other
---------------------
Rank Income Stores Cat =
IF(HASONEVALUE(DimStores[Store]),
SWITCH( TRUE(),
<= 10
<= 25
<= 50
Regular
Incompetent
)
---------------------
Star Unichar =
VAR star = UNICHAR(9733)
VAR star0 = UNICHAR(9734)
RETURN
REPT(star, 4) & star0
---------------------
Rank Store Income Cat Unichar =
VAR star0 = UNICHAR(9734)
RETURN
IF(HASONEVALUE(DimStores[Store]),
SWITCH( TRUE(),
[Rank Store Incomes] <= 10 , REPT(star,5),
[Rank Store Incomes] <= 25, REPT(star, 4) & REPT(star0, 1)
[Income Rank Stores] <= 50 , REPT(star,3) & REPT(star0,2)
[Store Income Rank] <= 100 , REPT(star,2) & REPT(star0,3)
REPT(star,1) & REPT(star0,4)), BLANK()
)
--------------------
Module: Selector with SWITCH
SUMX(FactSales, FactSales[Total Quantity] * RELATED(DimProduct[Unit Cost]))
[Total Revenues] - [Total Costs]
---------------------
Total Selection
IF(ISCROSSFILTERED(Selector[Selection]),
SWITCH(TRUE(),
Total Revenue
Total Costs
Total Profit
Total Revenues
)
If DayOfWeekLabel equals 'Friday' and DimCalendar[Day] equals 13, then 'Bad Luck', otherwise 'Normal Day'
= 1,0.05,0)
(Complete)], BLANK())
[Sales Amount],0)
Multiple
ntity] > 0), FILTER(FactSales, FactSales[ReturnType] = "Multiple")
General
0.05, RELATED(DimPromotion[DiscountPercent])
Discount
Total Revenue
Total Revenues
