Forum Discussion

GuestUser's avatar
GuestUser
Helper V
6 years ago

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

Need Help

10 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi GuestUser ,

     

    try this.

     

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

     

     PBIX

     

    • GuestUser's avatar
      GuestUser
      Helper V
      6 years ago

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