DAX query to exclude records from dataset

1
votes

I have to write a query to exclude records using DAX. Now I am not sure how would I exclude records based on a particular condition.

For example, I have to filter data and display employee data for a company in all states except New York. How would I achieve that?

It seems like I can only apply a filter to just display specific data and not for exclusion as you would do in SQL. In SQL we can just use a NOT IN (...) clause to do that. Is there something similar in DAX?

Any help would be greatly appreciated. Thanks!!

2
sqlssasdaxssas-tabular

2 Answers

2
votes
EVALUATE
CALCULATETABLE(
    <table expression>
    ,<table>[State] <> "New York"
)

The first argument need not be a table literal, but could be a function that returns a table.

The second argument should be on the table which contains the [State] field, and we simply exclude "New York". CALCULATETABLE() takes 1-N arguments. Arguments 2-N are all filters, which can be tables or simple predicates like in the example above. All filter arguments are evaluated in a logical and.

0
votes

It looks like you only need a filter if the state is New York, but if you need something equivalent of the SQL NOT IN you can use nested AND functions. For example 

EVALUATE
CALCULATETABLE(
    'EMPLOYEE',
     AND('EMPLOYEE'[STATE] <> "New York", AND('EMPLOYEE'[STATE] <> 
    "VIRGINIA", 'EMPLOYEE'[STATE] <> "MARYLAND"))
)