4. Filter Functions

🀄

4. Filter Functions

Status

Done

Filter Functions

CALCULATE, CALCULATETABLE
FILTER, ALL, ALLSELECTED, ALLEXCEPT, REMOVEFILTERS
KEEPFILTERS, VALUES, SELECTEDVALUE
CROSSFILTER, USERELATIONSHIP

Sample Dataset

We’ll use two tables: Sales and Customers

Sales Table

SaleID	CustomerID	Region	Amount	Date
1	C001	East	500	2023-01-05
2	C002	East	700	2023-01-10
3	C003	West	450	2023-02-15
4	C001	East	300	2023-03-20
5	C004	North	600	2023-04-25

Customers Table

CustomerID	Name	Segment
C001	Alice	Consumer
C002	Bob	Corporate
C003	Charlie	Consumer
C004	Diana	Home Office

Filter Functions: Python vs DAX Comparison Table

Function	DAX Syntax	Description	DAX Formula Equivalent	Python Expression
`FILTER`	`FILTER(table, condition)`	Returns a table that satisfies a condition.	`FILTER(Sales, Sales[Region] = "East")`	`sales[sales['Region'] == 'East']`
`CALCULATE`	`CALCULATE(<expression>, <filters>)`	Evaluates an expression in a modified filter context.	`CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East")`	`sales[sales['Region'] == 'East']['Amount'].sum()`
`CALCULATETABLE`	`CALCULATETABLE(table, filter)`	Returns a table with filter context applied.	`CALCULATETABLE(Sales, Sales[Amount] > 500)`	`sales[sales['Amount'] > 500]`
`ALL`	`ALL(column/table)`	Removes all filters from a column or table.	`ALL(Sales)` or `ALL(Sales[Region])`	`sales.assign(Region=None)`(simulate clearing filter)
`ALLSELECTED`	`ALLSELECTED(column)`	Returns all values visible via external report/visual filters.	`ALLSELECTED(Sales[Region])`	(Not directly applicable; simulates Power BI slicer visuals)
`ALLEXCEPT`	`ALLEXCEPT(table, column1, column2, ...)`	Removes all filters except the ones specified.	`ALLEXCEPT(Sales, Sales[Region])`	`sales.groupby('Region')[['Amount']].sum().reset_index()`
`REMOVEFILTERS`	`REMOVEFILTERS(column/table)`	Removes filters from specified columns or tables.	`REMOVEFILTERS(Sales[Region])`	`sales` (use full DataFrame with no filter applied)
`KEEPFILTERS`	Used inside `CALCULATE`	Applies filters without removing existing ones.	`CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Region] = "East"))`	`sales[sales['Region'] == 'East']`then apply additional logic
`VALUES`	`VALUES(column)`	Returns a table of unique values from a column.	`VALUES(Sales[Region])`	`sales['Region'].unique()`
`SELECTEDVALUE`	`SELECTEDVALUE(column, alternateResult)`	Returns the single selected value or an alternate result if multiple selected.	`SELECTEDVALUE(Sales[Region])`	`sales['Region'].mode()[0] if sales['Region'].nunique() == 1 else None`
`USERELATIONSHIP`	Used inside `CALCULATE`	Activates an inactive relationship between two columns.	`CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[CustomerID], Customers[CustomerID]))`	`pd.merge(sales, customers, on='CustomerID', how='inner')`(simulate inactive join)
`CROSSFILTER`	`CROSSFILTER(column1, column2, direction)`	Sets the direction of cross-filtering between related tables.		(No Python equivalent – handled via merge direction or model logic)