Forum Discussion

veryconfused's avatar
veryconfused
Frequent Visitor
6 years ago
Solved

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 exc...
  • richbenmintz's avatar
    richbenmintz
    6 years ago

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

     

    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!

veryconfused's avatar
veryconfused
Frequent Visitor
6 years ago

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.

richbenmintz's avatar
richbenmintz
Resident Rockstar
6 years ago

Hi veryconfused ,

 

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