CALCULATE
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is the CALCULATE Function?
The CALCULATE function is a powerful tool in DAX that allows you to modify the context in which a calculation is performed. In other words, you can use the CALCULATE function to change the filter context of a calculation, which can give you more control over your data. The basic syntax of the CALCULATE function is as follows:
CALCULATE(❰expression❱, ❰filter1❱, ❰filter2❱, ...)
The ❰expression❱ parameter is the calculation you want to perform, and the ❰filter❱ parameters are the filters you want to apply to the calculation. You can use any DAX expression as the ❰expression❱ parameter, including those that use other DAX functions.
Using the CALCULATE Function
There are many ways to use the CALCULATE function in Power BI, but we’ll explore some of the most common use cases below.
Filtering Data
One of the most common uses of the CALCULATE function is to filter data based on certain criteria. For example, let’s say you have a table of sales data and you want to calculate the total sales for a specific region. You can use the following formula:
CALCULATE(
SUM(Sales[Amount]),
Sales[Region] = "North"
)
This formula will calculate the total sales for the North region by filtering the Sales table to include only the North region.
Modifying Filter Context
Another common use of the CALCULATE function is to modify the filter context of a calculation. For example, let’s say you have a table of sales data and you want to calculate the total sales for a specific month, but you also want to include the sales from the previous month. You can use the following formula:
CALCULATE(
SUM(Sales[Amount]),
DATESBETWEEN(
Sales[Date],
DATEADD(
FIRSTDATE(Sales[Date]),
-1,
MONTH
),
LASTDATE(Sales[Date])
)
)
This formula will calculate the total sales for the current month and the previous month by modifying the filter context of the calculation to include the sales from the previous month.
Using Multiple Filters
You can also use the CALCULATE function to apply multiple filters to a calculation. For example, let’s say you have a table of sales data and you want to calculate the total sales for a specific region and product category. You can use the following formula:
CALCULATE(
SUM(Sales[Amount]),
Sales[Region] = "North",
Sales[Product Category] = "Electronics"
)
This formula will calculate the total sales for the North region and the Electronics product category by applying both filters to the calculation.
The CALCULATE function in Power BI is a powerful tool that can help you manipulate your data in many ways. By using the CALCULATE function, you can filter data, modify filter context, and apply multiple filters to your calculations. This can give you more control over your data and help you create more powerful visualizations and reports.
