Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GuestUser
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

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.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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

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.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @mwegener

 

Table alias I belong to item d

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

 

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

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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

 

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.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @mwegener 

 

We are using SSAS 2016

 

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

 

Any other option?

vivran22
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors