1

Can someone explain how the first filter statement is different from second in DAX?

    FILTER(Table, Table[Column] = "some text")

    FILTER(VALUES(Table[Column]), (Table[Column] = "some text"))
Sivabose
  • 15
  • 1
  • 4

1 Answers1

3

The FILTER function is a table function, meaning it will return a table. In the case of your second example, it is likely that you will get a scalar value (a single value) because you are filtering a table (of one column of unique values) by a single value. In the first FILTER instance, however, you will be returning an entire table of the first argument, which has only been filtered by the conditional in the second argument. For reference, I have used the sample data built within the Power BI Desktop version to show you the key differences.

From your first FILTER example

FILTER( financials, financials[Country] = "Germany" )

Output enter image description here

From your second FILTER example:

FILTER( VALUES( financials[Country] ), financials[Country] = "Germany" )
-- The extra parantheses around the second argument are not necessary...

Output

enter image description here

Therefore, the main functional difference is in the output. What are you wanting to return? An entire table or a specific value(s) of a column(s)?

codyho
  • 250
  • 1
  • 7
  • This isn't correct, In the second example we also get a table (with one value, but still a table). We can observe huge different in results for this two option if we use it as modifier in measures. – msta42a Jan 20 '22 at 10:54
  • Is it as simple as Selecting single column vs all column from filtered table? To Answer your question, I am trying to return a table. – Sivabose Jan 20 '22 at 17:23
  • @msta42a, you're right. A table and scalar value are not mutually exclusive. That is, you can have a table comprised of a scalar value (a single value). I have updated for clarity. – codyho Jan 21 '22 at 03:09