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

Ignore filter/selected value

I have a dashboard on SSAS model (Direct Query) that is build up on a view of 2 tables. one of the table represents Sales history with a blank forecast date and the other table represents the forecast with multiple forecast dates. I would like to have a comparision of Sales history Vs Forecast for all of the forecast dates. 

Data is stored as below in the SSAS Model :

SourceForecast DateHistoryForecast
Sales History 100 
Forecast5/26/2018 100
Forecast6/30/2018 50
Forecast7/28/2018 90

The dashboard should look like this. how do show a value for history even though the rows with history doesnt have a date.

Forecast dateForecastHistoryDifference
5/26/20181001000
6/30/20185010050
7/28/20189010010
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

You are going to need a measure, that measure will need to make use of the an ALL() function to ignore the filter context.

 

ALLEXCEPT() may need to be used instead, should there be some amount of filters to be kept.  For example you might have a location filter, but you still want data of all time.

 

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

You are going to need a measure, that measure will need to make use of the an ALL() function to ignore the filter context.

 

ALLEXCEPT() may need to be used instead, should there be some amount of filters to be kept.  For example you might have a location filter, but you still want data of all time.

 

View solution in original post

Highlighted

I was able to show the data as expected with the allexcept however  how can I ignore rowcontext but include filter on slicer. Below is my report. I'm able to show the history which has Blank forecast dates even when the forecast date is not selected. My formula includes filter on start month and product line. Everything works as expected. However I want to use the weighted average of the total history selected in the report. But the report context is coming into picture here. Last column is the sum of the history for the selections made. I want to show the sum as 159405  in all rows. However the sum is shown 27108 for 003 and 132297 for 006. How can I override the row context but make sure the product line filter in the slicer is applied. Below is the formula I have for the measure test

test = CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]), ALLEXCEPT(V_FORECASTACCURACy,V_FORECASTACCURACY[PROFITCENTERBRAND],V_FORECASTACCURACY[STARTMONTH],V_FORECASTACCURACY[STARTWEEk],US_MATERIAL[Product Line])) 

Capture.PNG 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors