SELECT in WHERE clause equivalent in Power BI

[ltekchandani]

Hi,

I have a table with following columns

user_id, product.

Sample Data:

Buyer ID	Product
1	Avocado
1	Bread
1	Cream Cheese
1	Ham
2	Avocado
2	Bread
3	Ham
3	Bread
3	Chicken

I want to recreate this SQL query in PowerBI:
SELECT product, COUNT(DISTINCT buyer_id)

FROM table

WHERE buyer_id IN (SELECT buyer_id FROM table WHERE product = @@product)

;

For example,
if I select "Avocado" in a filter Pane, I want to display how many users who buy avocado also bought other products. Here's the sample expected visual:

Product	Total Buyers	Overlap Buyers
Avocado	2	2
Bread	3	2
Cream Cheese	1	1
Ham	2	1
Chicken	1	0

In this visual,
Total Buyers for "Chicken" is 1 because Buyer_id 3 bought chicken. But Overlap Buyers is 0 because even though buyer_id 3 bought chicken, they did not buy avocado.

I have tried multiple methods like nested FILTERS, creating a separate table but it doesn't solve my issue. Can someone please help in developing a DAX equivalent for the SQL query above?

[v-chenwuz-msft · Accepted solution]

Hi @ltekchandani 

This is some thing like Association Analysis. 

In power bi desktop, you need to create a new table to get product field for slicer due to having no inactive with table visual which you used to display the result. Via the following expression:

Product = VALUES('Table'[Product])

 After that , to display Total Buyers drag the Buyer ID field into table visual and set it Count(distinct)

Or this measure :

Total Buyers = DISTINCTCOUNT('Table'[Buyer ID])

Then the following measure to calculate overlap buyers:

Overlap Buyers =
VAR _s_filter =
    FILTER( ALL( 'Table' ), [Product] = SELECTEDVALUE( 'Product'[Product] ) )
VAR _1 =
    DISTINCT( 'Table'[Buyer ID] )
VAR _2 =
    GROUPBY( _s_filter, [Buyer ID] )
VAR _3 =
    DISTINCT( UNION( _1, _2 ) )
RETURN
    COUNTROWS( _1 ) + COUNTROWS( _2 ) - COUNTROWS( _3 )

For the slicer visual , use new table product[product] as it's vaule. 

the result:

I put my pbix file in the attachment you can refer

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ltekchandani , Create measure like first one , if you have independent product table, Seond if product is joined with table or used from tbale 

measure =
var _Tab = summzarize(filter(Table, Table[Product] in allselected(product[product])), Table[Buyer])
return
calculate(distinctcount(Table[buyer Id]), filter(Table, Table[Buyer] in _tab))

measure =
var _Tab = summzarize(filter(all(Table), Table[Product] in allselected(product[product])), Table[Buyer])
return
calculate(distinctcount(Table[buyer Id]), filter(all(Table), Table[Buyer] in _tab))

plot with product in visual