How to make a Calculated Table dynamic based on a Slicer

[GertjanGerits]

For the visualisation of a waterfall graph (Simple Waterfall), we make a calculated table containing two columns: one consisting of the loss categories, and the other the quantity of the loss (see picture). These columns are formed by adding parts of columns from different Facts tables from the rapport and measures based on these Facts tables. All those Facts tables are linked in the model to a Calender table.

Now, I installed a slicer for the dates from the Calender table to filter the measures and tables for different ranges of time. When I look at the measures i, the rapport on card visuals, I can see that it works for the measures. 

However, in the calculated table, the filter from the slicer does not work and the table is always calculated for the whil range of dates in the Calender table. I assume by checking the internet and this forum that is caused since calculated tables are formed when data is refreshed and therefore not influenced by the filters.

However, is there a way that I can bypass this and still be able to, in one rapport, change the timerange and let the waterfall graph follow the selected range in the slicer?

[DataNinja777]

Hi @GertjanGerits 

I think you don't need to have the calculated table to be filtered by the slicer in order to get the waterfall chart to respect the time filter of your slicer selection.  Your waterfall chart which is based on the measures from fact tables linked with the calendar table will be filtered according to the selection you make in the slicer from a field in the calendar table.  

Best regards,

[talespin]

hi @GertjanGerits 

Slicers can't filter the calculations performed in your Calculated Table or calculated columns.

Option1 : Include columns in your Calculate table that you want to filter using slicers.

Option2 : Do the calculations in a measure.