Distinct between 2 tables

[farinabrn]

Hi everyone,

I am start to learning PBI and on my work i have a problem, Can you help me?

I'm trying to build a dashboard with a stacked bar chart where will be shown a percentage of stores with missing products per week_number, in other words, i need to distinct the ID of PRODUCT relation with PRODUCT_ID grouped in SALES relation, this relation will be the NOT SOLD product. Following below an example:

 

A SALES table contains info of each product sold every day, with the columns: product_code, sale_quantity, volume, date that are grouped by the fields: year, week_number, region, company, store and PRODUCT_ID. Then Product table has all product registers.

I would to do a calculated table trying group the SALES table using SUMMARIZE like that:

SALES_GRP = SUMMARIZE(SALES; SALES[YEAR]; SALES[WEEK]; SALES[REGION]; SALES[COMPANY]; SALES[STORE])

and make a relation with SALES (I try the SUBSTITUTEWITHINDEX but didn't work) to catch a table of one column containing all week products to use EXCEPT and ALL in DAX some like that:

DISTINCTCOUNT (
   EXCEPT(
      ALL( PRODUCT[ID] ),
      "PRODUTCTS SOLD IN A WEEK PER STORE.."
   )
)

I have wrote this sql query trying to find a way to convert it to DAX but i'm noob lol

SELECT v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE
((SELECT COUNT(1) FROM PRODUCT) - COUNT(DISTINCT p.ID)) AS prod
FROM SALES v
LEFT JOIN PRODUCT p ON p.ID = v.PRODUCT_ID
GROUP BY v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE

[v-ljerr-msft · Accepted solution]

Hi @farinabrn,

---

I have wrote this sql query trying to find a way to convert it to DAX but i'm noob lol

SELECT v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE
((SELECT COUNT(1) FROM PRODUCT) - COUNT(DISTINCT p.ID)) AS prod
FROM SALES v
LEFT JOIN PRODUCT p ON p.ID = v.PRODUCT_ID
GROUP BY v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE

---

Instead of converting the sql query to DAX, you should be able to directly use the sql query in SQL statement option when GetData to a new table to your model. 

Regards