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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
veryconfused
Frequent Visitor

Slicer Filter Interaction With Chart

I have a weekly report that needs to show data from that week, controlled by a slicer set as a dropdown (ex, Week Ending 8/9).

 

The issue is that I need all visualizations to follow the slicer except I would like a chart showing the last 8 weeks of sales. I can remove interaction between chart and slicer - the problem is, if the end consumer changes the slicer (ex, they want to look at Week Ending 7/26), I want the chart to show the last 8 weeks of sales from 7/26 backwards. With interaction removed, this doesn't happen, the chart is static.

 

I suppose I want the slicer to be a filter on most of the report, but only drive the end date of the chart.

 

Is this possible? I'm trying to avoid having a second slicer for the chart, with the user having to remember to update two slicers.

1 ACCEPTED SOLUTION

Hi @veryconfused ,

 

please try this measure, it will show the dynamic weeks on the x axis, as an aside your data seems to be the same for each week, why all the bars are the same height.

z.Dynamic X Axis = 
    VAR SelectedWeek = max(fSales[Week End Date (Saturday)])
    VAR FloorWeek = max(fSales[Week End Date (Saturday)]) -21
RETURN
    IF(
        HASONEVALUE(Weeks[Week End Date (Saturday)]) && 
        VALUES(Weeks[Week End Date (Saturday)]) >= FloorWeek &&
        VALUES(Weeks[Week End Date (Saturday)]) <= SelectedWeek,
            CALCULATE(sum(fSales[TY Net Sales]),
               fSales[Week End Date (Saturday)] =  values(Weeks[Week End Date (Saturday)])),
            BLANK()
    )

richbenmintz_0-1598061913013.png

richbenmintz_1-1598061953736.png

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
veryconfused
Frequent Visitor

Hi @richbenmintz 

 

Had to whip up some dummy data. Posted at the link below. Thanks again.

 

(Edit: Below link updated w/ data showing different sales per week.)

 

https://www.dropbox.com/s/lxfoxejuh43kzmt/DummyReportSlicers.pbix?dl=0 

Hi @veryconfused ,

 

please try this measure, it will show the dynamic weeks on the x axis, as an aside your data seems to be the same for each week, why all the bars are the same height.

z.Dynamic X Axis = 
    VAR SelectedWeek = max(fSales[Week End Date (Saturday)])
    VAR FloorWeek = max(fSales[Week End Date (Saturday)]) -21
RETURN
    IF(
        HASONEVALUE(Weeks[Week End Date (Saturday)]) && 
        VALUES(Weeks[Week End Date (Saturday)]) >= FloorWeek &&
        VALUES(Weeks[Week End Date (Saturday)]) <= SelectedWeek,
            CALCULATE(sum(fSales[TY Net Sales]),
               fSales[Week End Date (Saturday)] =  values(Weeks[Week End Date (Saturday)])),
            BLANK()
    )

richbenmintz_0-1598061913013.png

richbenmintz_1-1598061953736.png

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@richbenmintz 

 

Fantastic, thank you! Seems I had reversed the disconnected and connected date columns in your measure formula.

 

The sales each week were the same on the dummy data (put it together too fast) - I updated the link above for a version with varying sales data per week in case this thread helps anyone else in the future.

veryconfused
Frequent Visitor

Thanks to all for posting replies, and links to forums resources. I have been working on this for hours and can't quite figure the solution..

 

@Greg_Deckler  and @richbenmintz :

 

I have tried both of these solutions with the same outcome.

  • Created a disconnected dates table for both solutions, with a single column of the date.
  • The slicer is using the date from my sales table, not the disconnected date - tried using the disconnected date for the slicer and got no results
  • In both cases, the chart now shows the desired weeks back on the x axis
  • However, the value measure I'm trying to calculate (sum of sales) shows the total for all 3 weeks in each week, rather than each week's total.
    • I feel like this must have something to do with using the disconnected date table and I can't quite understand how to square it.
    • To note, in either case I'm calling a measure I created earlier that's simply SUM(Net Sales). I also tried to write it SUMX(SalesTable, Net Sales) to no change.

Any ideas on what I'm missing here greatly appreciated.

Hi @veryconfused ,

 

please share your .pbix file, then I/we would be able to create the appropriate measure with your data and model.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


richbenmintz
Resident Rockstar
Resident Rockstar

Hi @veryconfused,

 

You will need a disconnected Week Dimension that you can use as the X Axis for your Chart. Then you would create a measure like the one below: 

Dynamic X axis = 
    var selectedWeek = max('calendar'[Week])
    var floorWeek = max('calendar'[Week])-6
return
    if
        (
            HASONEVALUE('Calendar dynamic'[Week]) && VALUES('Calendar dynamic'[Week]) >=floorWeek && VALUES('Calendar dynamic'[Week]) <= selectedWeek, 
            CALCULATE(SUM(FTE[FTE]), 'Calendar'[Week] = VALUES('Calendar dynamic'[Week]))
            ,BLANK()
        )

 

Essentially you are assigning a value where the disconnected week is between the floor and ceiling weeks, otherwise Blank() is returned, leaving you with a chart that looks something like

richbenmintz_0-1598045088965.png

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Greg_Deckler
Community Champion
Community Champion

@veryconfused - You want The Complex Selector. The example solved is almost exactly your scenario:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

 

Also, since I see you are a New Member, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors