How to cancel a filter that is being applied by a slicer from a Relational Table

[tmendoza]

Hello All,

I'm trying to get the amount for a prior period that is being selected in a slicer.

The data in my slicer is from a related table that has a 1 to many relationship with the table I'm trying to get an amount from.

The two tables are related by period. One table is literally just a column with periods, no duplicates. The other table is the full data set.

I'm using a measure to derive the previous selected period amount. FYI: SnapShot = Period

PriorValues2 = 
VAR __SnapShotSlicer = INT(SELECTEDVALUE('SnapShot Tbl'[SnapShot])-1)
RETURN
CALCULATE(SUM('Waterfall by Category'[Value]), FILTER(ALL('Waterfall by Category'[SnapShot]), VALUE('Waterfall by Category'[SnapShot])= __SnapShotSlicer))

My amount is comming out to a "blank". However, If my measure is looking for the snapshot selected, everything works!

PriorValues2 = 
VAR __SnapShotSlicer = INT(SELECTEDVALUE('SnapShot Tbl'[SnapShot]))
RETURN
CALCULATE(SUM('Waterfall by Category'[Value]), FILTER(ALL('Waterfall by Category'[SnapShot]), VALUE('Waterfall by Category'[SnapShot])= __SnapShotSlicer))

This is because the slicer is filtering the data set for the other table because they are related and only the period shown in the slicer is unfiltered in the other table (Waterfall by Category). This is making it imposible to derive the amount from the previous period that is being selected. Thus, drawing a blank.

I can deactivate the relationship between the two tables to make this work, but I need these tables to hold their relationship.

Does anyone know what I can do to undo the filter being caused from the slicer so I can pull the previous periods amount with my measure?

Thanks!!!

Tom

[tmendoza · Accepted solution]

Hey guys,

So after a ton of work on this, I've come to realise that it's just not possible to get the amount of the prior period selected in the slicer using DAX.

The reason is because the data table (in the background) that the slicer is filtering is litteraly getting filtered to the selected periods only. Thus, the data chart in the background will only show the periods that have been selected in the slicer and no calculations can be made that require periods out side of the slicer. So finding the amount for the previous period selected was not possible through DAX. What I had to do is create a reference table of the same data set and deactivate it's relationship to the slicer. I did this, so my reference table wouldn't filter to the whim of the slicer.

So when I wanted the amount of the prior period that was being selected, I chose to pull from the amount of the reference table instead of the original data set that was being filtered.

It's more data than I want, but it works.

Tom

[Greg_Deckler]

So a couple of potential ways, you could Edit Iteractions so that your slicer does not affect your one visual. Otherwise, you will need to do something like putting an ALL or ALLEXCEPT in and change the filter context.

[tmendoza]

Hi Greg,

Thanks for the reply!

In this dashboard I'm making I need all vizuals to opperate off the one slicer.

I'm using ALL in my formula; also, ALLEXCEPT brings the same result. Are you saying I should use these differently?

What do you mean, by changing the filter context? Do you have a DAX formula that you could use as an example?

Thanks so much.

Sorry about all the questions, I'm just trying to fully understand since I'm still learing this platform.