cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DAX Query Help!

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

10 REPLIES 10
Highlighted
Super User II
Super User II

Re: DAX Query Help!

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





Did I answer your question? Mark my post as a solution!

Kudos will be cherry on the top 🙂


Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: DAX Query Help!

Hi @GuestUser ,

 

try this.

 

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

 

 PBIX

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Helper V
Helper V

Re: DAX Query Help!

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

Highlighted
Super User IV
Super User IV

Re: DAX Query Help!

Hi @GuestUser ,

 

which table does the table alias i belong to?

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Helper V
Helper V

Re: DAX Query Help!

Hi @mwegener

 

Table alias I belong to item d

Highlighted
Helper V
Helper V

Re: DAX Query Help!

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

Highlighted
Super User IV
Super User IV

Re: DAX Query Help!

Hi @GuestUser 

 

check this.

 

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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Helper V
Helper V

Re: DAX Query Help!

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?

Highlighted
Super User IV
Super User IV

Re: DAX Query Help!

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/

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Users online (727)