ALLCROSSFILTERED
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 ALLCROSSFILTERED?
ALLCROSSFILTERED is a DAX function that removes filters from a table or column, and then applies a new filter to the same table or column. This function returns a table or column, or a set of tables or columns, without any filters, but with a new filter applied. The ALLCROSSFILTERED function is commonly used in Power BI to create complex filters that can be applied to a variety of different data sources.
How to Use ALLCROSSFILTERED
To use the ALLCROSSFILTERED function in Power BI, you will need to follow a few simple steps. These steps are outlined below:
1. Decide which table or column you want to apply the ALLCROSSFILTERED function to.
2. Write a DAX formula that uses the ALLCROSSFILTERED function to remove filters from the table or column.
3. Apply the new filter to the table or column using the ALLCROSSFILTERED function.
It is important to note that the ALLCROSSFILTERED function can be used in conjunction with other DAX functions to create more complex filters. For example, you can use the ALLCROSSFILTERED function with the CALCULATE function to filter data based on multiple criteria.
Examples of ALLCROSSFILTERED in Action
Let’s take a look at a few examples of how the ALLCROSSFILTERED function can be used in Power BI.
Example 1
Suppose you have a table called “Sales” that contains data about sales transactions. You want to create a measure that calculates the total sales for each product category, but you want to exclude any filters that have been applied to the “Product” column.
To do this, you can use the following DAX formula:
Total Sales by Category =
CALCULATE(
SUM(Sales[SalesAmount]),
ALLCROSSFILTERED(Sales[Product])
)
This formula removes any filters that have been applied to the “Product” column, and then calculates the total sales for each product category.
Example 2
Suppose you have a table called “Orders” that contains data about customer orders. You want to create a visual that shows the total number of orders for each customer, but you want to exclude any filters that have been applied to the “Order Date” column.
To do this, you can use the following DAX formula:
Total Orders by Customer =
COUNTROWS(
FILTER(
ALLCROSSFILTERED(Orders[Order Date]),
Orders[Customer ID] = SELECTEDVALUE(Orders[Customer ID])
)
)
This formula removes any filters that have been applied to the “Order Date” column, and then filters the data based on the selected customer. The COUNTROWS function then calculates the total number of orders for that customer.
The ALLCROSSFILTERED function is a powerful tool in Power BI that can help you create complex filters that can be applied to a variety of different data sources. By using the ALLCROSSFILTERED function in your DAX formulas, you can remove filters from a table or column, and then apply a new filter to the same table or column. This can be useful when you want to filter data in a more efficient and effective way.
