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 report in below format
Month Forecast Quantity
Jan 100
Feb 200
Now I have 3 tables, Fact-Forecast and Dim-Item and Dim-Date
ForecastQuantity Measre = sum(Fact-Forecast.Forecast)
The expected value is not coming since i need to put the below filter condition(subquery)
Can you pls help on how to write forecast quantity measure in DAX?
below is the sql query which i need to write in DAX
select sum (f.forecast)
from
fact-forecast f,
dim-item d
where
f.item_id = d.id
and i.item_description in
(select distinct f.article_desc
from
fact-forecast f
);
Hi @GuestUser ,
try this.
Forecast Quantity = SUMX(FILTER('Fact-Forecast','Fact-Forecast'[article_desc] = RELATED('Dim-Item'[item_description])), 'Fact-Forecast'[forecast])
Thanks @mwegener
I guess i was not clear before..
But the value isn't matching . Actually when i applied the DAX formula provided , the data is equivalent to below sql query (
filter of month in subquery)
select sum (f.forecast)
from
fact-forecast f,
dim-item d,
dim-date date
where
f.item_id = d.id
and f.date_wid = date.id
and date.month = 'April-2020'
and i.item_description in
(select distinct f.article_desc
from
fact-forecast f
where date_wid = '0420'
);
But i need DAX expression for below sql query
Should not take the month filter in the subquery.
Ideally the data in the table is such a way that for same item_wid - item description is different in dimension and fact table
Can you pls suggest?
select sum (f.forecast)
from
fact-forecast f,
dim-item d,
dim-date date
where
f.item_id = d.id
and f.date_wid = date.id
and date.month = 'April-2020'
and i.item_description in
(select distinct f.article_desc
from
fact-forecast f);
Pls find below proper query:
select sum (f.forecast)
from
fact-forecast f,
dim-item d,
dim-date date
where
f.item_id = d.id
and f.date_wid = date.id
and date.month = 'April-2020'
and d.item_description in
(select distinct f.article_desc
from
fact-forecast f);
Hi @GuestUser
check this.
Forecast Quantity = CALCULATE(SUM('Fact-Forecast'[forecast]),FILTER('Dim-Item', 'Dim-Item'[item_description] IN ALL('Fact-Forecast'[article_desc])))
Thanks @mwegener
But since I am using Connect Live-- this IN operator is giving syntax error
Is there any other way of writing this DAX Query?
Hi @GuestUser ,
Which SSAS version did you use?
Forecast Quantity = CALCULATE(SUM('Fact-Forecast'[forecast]), FILTER('Dim-Item', CONTAINSROW(ALL('Fact-Forecast'[article_desc]), 'Dim-Item'[item_description])))
Hi @mwegener
We are using SSAS 2016
contains function is present but containsrow function is not there.:(
Any other option?
Hello @GuestUser
Can you share some sample data along with the expected output?
This would help in providing appropriate solution.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |