cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
macpow
Frequent Visitor

Ignore visual level filter used in table visual

Hi

 

Really hoping someone can help with this.

 

I have a dataset that is tracking a number of items that make up a project. Once every 6 months a report is submitted that shows the updated completion rate of each item in the project (the reports also contain other metrics but that aren't relevant here). I need to have a table visual that shows the metrics, including completion rate, for each item and need to show the most recent report data in the table visual.

 

To be able to always have the most recent data showing only I have used the following calculated column:

LatestPPR = IF('Local Supply_PPR_1'[MaxPPR] > 'Local Supply_PPR_1'[Date], 0, 1)

MaxPPR in the above is another calculated column that has the date of the most recent report so I can check it in LatestPPR against the date of that report. Then I just use this as a visual level filter. All of this works.

 

However, I also need to calculate the variance in the completion rate for each item between the current report and the previous report (ultimately I would like to be able to allow the user to select a range of reports to see the variance across them but that is for down the track).

 

To do that I have the following measure:

% of Completion Variance (from last PPR) = 
var PrevValue = CALCULATE(SUM('Local Supply_PPR_1'[% of Completion (D)]), FILTER('Local Supply_PPR_1', 'Local Supply_PPR_1'[PPR# (Value only)] = MAX('Local Supply_PPR_1'[PPR# (Value only)]) - 1))
var CurrentValue = CALCULATE(SUM('Local Supply_PPR_1'[% of Completion (D)]), FILTER('Local Supply_PPR_1', 'Local Supply_PPR_1'[LatestPPR] = 1))
return
    CurrentValue - PrevValue

If I use this in a table that has no visual filter then it works (ie. Item A has a completion rate of 30% in the current report, and a completion rate of 26% in the previous report so the above returns 4% which is correct). But if I place it in to a table that has the LatestPPR visual filter it doesn't work. For example, Item A shows a variance of 30% rate from the previous report.

 

I figure that with the visual filter it's filtering the data down so that there is essentially no previous report data present in the table so when it goes to calculate the variance it is calculating the current report completion rate minus nothing. That makes sense to me but I can't figure out how to resolve that. I've tried ALL and REMOVEFILTERS but to no avail. I've tried using all in the FILTER statement in both variables, I've tried using it outside of the FILTER statement in both variables but nothing is working.

 

Does anyone have any ideas here? I feel like I can get the theory (ie. I need to ignore the visual level filter on the PrevValue variable and then set a new filter context to get the previous report data) but I'm pushing at the limits of my knowlege of DAX here and can't work out how to formulate the measure to get what I need.

 

Any help would be greatly appreciated.

 

Thanks

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@macpow , if you want two date ranges two work, refer to this example

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

Based on independent date table. Sometime we can get thing work better with independent tables.

 

For standard period like month, quarter and week you can do MON, YOY and QOQ with single date table

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit

 

Thanks for the reply. I've considered using time intelligence functions here but in this case they won't resolve the issue I'm having.

 

Thanks

@macpow , one case I shared of two date range. Hope that will also not work.

Can you please share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors