Make slicer Ignore X-axis while affecting measure

DAX DAX Calculations
1

Help.pbix (61.3 KB)

Hi, I need help finding a way to keep an X-axis static in a visual that uses a measure which needs to accept filter context from the same date table that is feeding the X-axis. I have attached an example PBI file. Also uploaded images to help drive the point.

Slide1
Slide11280×720 87.7 KB

Slide2
Slide21280×720 87.9 KB

Slide3
Slide31280×720 28.1 KB

In the file, you will see a month/year slicer that uses a field from the DIM_Date table, a table visual that lists records within my FCT_Results table, and then a clustered column chart that uses a measure that counts the results with “N” as the value.

I would like to be able to manipulate the date slicer to show only the question IDs that had “N” during the month-year selected but I don’t want the x axis to change at all because I want users to see the “trend” over time of the question IDs that had “N” for the month they select

Hope that makes sense, if not I can clarify more

Thanks for the help

2

@Austinjdonahue

You really pushed my creativity on this one :sweat_smile: Firrst I tried and tried but didn’t come up with a solution and thought; this is impossible. But I couldn’t let go and ultimately find a solution, I think.

Here you go:
After trying a lot of different things with removing filter context etc I concluded it wasn’t possible in the current ‘datamodel’ / ‘datastructure’ ).

On the X axis, there will always be a filter context for that specific month. You can either remove it with ALL / REMOVEFILTER, but you can’t redirect it to another month (the selected month of your slicer).

So, then the creativity came in and I made a duplication of your current date dimension. By doing so, one can be used for the x axis and the other can be used for the slicer.

I named all my measures with DM at the end and I hope you can grasp the idea and the logic of what I have done. I think it should fix your problem / question.

Feel free to ask if things are unclear :slight_smile:

Help_SolutionDashboardingMeesters.pbix (77.1 KB)

Kind regards,
Pim Meesters

2 Likes
3

This is exactly what I was looking for. Thank you so much!

Your final solution is a symphony of every route I attempted to go down but just couldn’t combine everything to come up with the solution. I had a duplicated date table at one point but then got stuck, I tried making tables with selected value filter, etc

This was great, thanks!

1 Like