How to immitate NOT IN using DAX?

Question

I am new to DAX and I need to be able to achieve something like:

SELECT COUNT(distinct buyer_id)

FROM tblBuyers

WHERE status LIKE 'Active' AND category = 2 AND buyer_id  NOT IN (SELECT buyer_id FROM tblBuyers WHERE category = 1)

This would usually be easy to achieve, but bear in mind that tblBuyers has got multiple rows per buyer, and each buyer may have more than one category. Basically I am trying to get a coutn of buyers who are Actve and have category 2, but do not have category 1(buyers in this table have multiple categories). How can I achieve this in DAX?

Answer 1

Take a look at this blog post:

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/19/powerpivot-basket-analysis-and-the-hidden-many-to-many.aspx

Then check the Basket Analysis pattern in the Many-to-Many relationships whitepaper:

http://www.sqlbi.com/articles/many2many/

And finally optimize the query by using the SUMMARIZE pattern:

http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/

You will see the same pattern applied - fundamentally, you need to write a CALCULATE with two filters.

Marco

---

Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

Answer 2

Marco...Thank you.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 3

an other option would be to use CONTAINS(...) or, in your case NOT(CONTAINS(...))

hth,
gerhard

---

- www.pmOne.com -

Answer 4

an other option would be to use CONTAINS(...) or, in your case NOT(CONTAINS(...))

hth,
gerhard

---

- www.pmOne.com -

But Contains returns a boolean value?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 5

yes, a boolean value which can be used in a FILTER-function

i described a similar scenario here where CONTAINS is used to simulate a SQL IN

http://gbrueckl.wordpress.com/2012/05/21/consolidation-and-intercompany-elimination-made-easy-using-powerpivot-and-dax/

---

- www.pmOne.com -

Answer 6

yes, a boolean value which can be used in a FILTER-function

i described a similar scenario here where CONTAINS is used to simulate a SQL IN

http://gbrueckl.wordpress.com/2012/05/21/consolidation-and-intercompany-elimination-made-easy-using-powerpivot-and-dax/

---

- www.pmOne.com -

Great post Gerhard. Thx.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 7

Is this correct?

=CALCULATE(DISTINCTCOUNT(buyer_id),FILTER(tblBuyers, tblBuyers[status]='Active' && tblBuyers[category]=2 && NOT(CONTAINS(tblBuyers,tblBuyers[category]=1)) ))

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 8

try this one:

inC2active_notC1:=CALCULATE([DC_buyer], 
FILTER(tblBuyers, 
    tblBuyers[status]="Active" 
    && tblBuyers[category]=2 
    && NOT(CONTAINS(FILTER(tblBuyers, tblBuyers[category]=1), tblBuyers[buyer_id], tblBuyers[buyer_id]))))

first talBuyers is filter on status="Active" and category=2
and then it is further checked, if the current buyer_id matches any buyer_id of category=1

FILTER(tblBuyers, tblBuyers[category]=1) returns only rows of tblBuyers where category=1
CONTAINS allows you to check if the tblBuyers[buyer_id] of the current row of the outer iterator (first FILTER()) is contained in the inner tblBuyers which is filtered by category=1

here is a little sample-table that i used:

buyer_id	status	category
1	Active	C1
1	Active	C2
1	Active	C3
2	Active	C2
2	Inactive	C3
3	Active	C2
3	Inactive	C3
4	Inactive	C1
4	Active	C2
4	Active	C3

hth,
gerhard

---

- www.pmOne.com -

Answer 9

Gerhard, Thank you!

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog