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

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!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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 Kudoed Authors