Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |