SQL to DAX (GROUP BY, JOIN + COUNT)

0
votes

Market Basket Analysis

Hi! I'm doing a Market Basket Analysis in Power BI. I'm not well versed in DAX so I was wondering what is the equivalence of this SQL Code in DAX:

SELECT order_id, COUNT(product_id), product_name
FROM products p
INNER JOIN order_products_train ot
 ON ot.product_id = p.product_id
GROUP BY order_id

So basically, I want to count the frequencies of each product in an order

Any thoughts?

2
sql-serverpowerbidax
Side note: It makes no sense to select COUNT(product_id), because that will always be 1, given your current GROUP BY. You probably intended to group only by the order_id. - Tim Biegeleisen
Oops, my bad. I just wrote this while writing this post. Thanks for noticing! - Hibari

2 Answers

0
votes

This SQL query won't work as product_name is not in the group by. Normally you won't need any DAX to do this, just drag those fields into a table and select count as aggregation for the product_id field (given that you have this join as a relationship).

0
votes

Since the columns don't have their table aliases in your example, I'm making some assumptions on which columns belong to each table, but the DAX equivalent of what you posted would be along the lines of the following example. As @analzethat noted, this assumes the proper relationship is in place between the tables.

EVALUATE
SUMMARIZECOLUMNS (
    Order_Products_Train[Order ID],
    Products[Product Name],
    "Product Count", COUNT(Products[Product_ID])
)