DAX Query Help!

[GuestUser]

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
);

[mwegener]

Hi @GuestUser ,

try this.

Forecast Quantity = SUMX(FILTER('Fact-Forecast','Fact-Forecast'[article_desc] = RELATED('Dim-Item'[item_description])), 'Fact-Forecast'[forecast])

 PBIX

[GuestUser]

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);

[mwegener]

Hi @GuestUser ,

which table does the table alias i belong to?

[GuestUser]

Hi @mwegener

Table alias I belong to item d

[GuestUser]

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);

[mwegener]

Hi @GuestUser 

check this.

Forecast Quantity = CALCULATE(SUM('Fact-Forecast'[forecast]),FILTER('Dim-Item', 'Dim-Item'[item_description] IN ALL('Fact-Forecast'[article_desc])))

[GuestUser]

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?

[mwegener]

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])))

https://dax.guide/containsrow/

[GuestUser]

Hi @mwegener 

We are using SSAS 2016

contains function is present but containsrow function is not there.:(

Any other option?

[vivran22]

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