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 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
Solved! Go to 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
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
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |