FILTER - DAX Guide

FILTER: Returns a table that has been filtered.

This video is part of DAX Guide, the online guide to all the DAX functions, with links to related content and compatibility matrix with all the versions of Power BI, Azure Analysis Services (AAS), SQL Server Analysis Services (SSAS), SQL Server Data Tools (SSDT), and Power Pivot for Excel.


#dax #powerbi #ssas #tabular #analysisservices #powerpivot

[kostaszogo]

Great video as always Alberto!
I have a question though: Is there a way to filter by using only part of the value and not the whole word? For example instead of finding all customers for which [Continent] ="North America", can we find all customers for which [Continent] contains the word "America" (in order to get both South and North America)?
Would appreciate a response

[SBridge]

If i do the equivalent of your query1 i get the following error "he Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scalar Value" My dax is exactly the same as yours so i don't get it?

[MilhouseBS]

Nice and dense. I like comparable CALCULATE with KEEPFILTERS as comparable to FILTER version.

[MrMalorian]

And here I thought I just use the filter options as the back end of Calculate. Good to know.

[workstuff]

Is it bad practice to filter a fact table (2-3M rows)? For content, the filter is on a junk type dimension on the fact table for order type. FIlter( Sales, Sales[Type]="Involved")

[skovacsbarna]

Hello, how can I filter the date in the columns of a matrix or a table, to get the 1st or 2nd week of the selected month as a calendar? From Monday to Sunday. Thanks.

[arturoramirez]

If you filter the actual customer table by selecting Europe in the Continent column we get 18869 rows and 5546 filtered rows. Why two different figures? I assume every record is a different customer. Intuitively there are 18869 European customers because there are 18869 records in the table that have Europe as the continent. Also your first query in this video says 5546 filtered rows in the bottom right corner, So again why two different figures? Thank you

[rick_.]

Hey, Alberto, these are very useful! Question about Query Tab #2: On row 11 you multiply Sales[Quantity] * Sales[Net Price]. On row 14 you use the [Sales Amount] measure. Could you explain why?

[eagillum]

Is it possible that I need to remove a previous filter before reapplying another one in the same formula? I have an if statement: IF (IS EMPTY(VALUES....)) which means "if a filter has been applied to a table and there is no row matching that filter, then do I need to remove that filter so I can filter for another result in that same table?

[imashiah]

Question please. At 3:28 you say you cannot use CALCULATE to apply a filter to the column "Monthly Sales" that was generated with the ADDCOLUMNS function. Rather, you need to use FILTER. Doesn't every CALCULATE always nest over FILTER(ALL( making it able to filter to the column "Monthly Sales" in this case? Could this be the unique effect coming from ADDCOLUMNS; as you say the lack of data-lineage?

[nancygl]

Can anyone please explain the difference of using FILTER in CALCULATE. For example, the CALCULATE( [Total Customer], FILTER (Customer, Customer[Continent] = "Europe")) and CALCULATE( [Total Customer], Customer[Continent] = "Europe") results the same. And FILTER being an iterator function, I suppose it would have performance issue for larger datasets. But how do we decide, if not large dataset, or what is the best practice or am I loosing some context here?