What if Parameter not work when applying it with filter to a table

[Weijia]

Hi there,

I have a table with a year column from 2021 to 2050, they are formated as whole number. I want to use dax to create two tables that dynamically split the year table. Generally speaking, if the end user want to select a year, for example, 2030, then one dax could genrate one table from 2021 to 2030 and the other one could get the table from 2031 to 2050.

I created a what if parameter to generate a year list from 2021 to 2050 without default year value. They are formatted as whole number as well.

Then I used dax to generate a table "year less" which is for years less than or equal to selected value from the parameter. From the screenshot below can be seen that the target year has been selected for 2032. But based on the following dax, the year less table returned a blank table, which means the filter option did not work.

The dax for what if parameter is:  year what if parameter = GENERATESERIES(2021, 2050, 1)

target year = SELECTEDVALUE('year what if parameter'[year range])

The dax for year less table is:  year less = FILTER('year table', 'year table'[year] <= 'year what if parameter'[target year])

If I set the what if parameters default to a number, no matter what year I selected from the input section, the dax always return a table based on the default value, for example, if the default value is 2030, then the dax table get back to 2030 even the target input selected as 2032.

Anyone has any thoughts? Thanks in advance. @amitchandak @Greg_Deckler @AllisonKennedy 

[AllisonKennedy · Accepted solution]

@Weijia  as @AlexisOlson already mentioned, Tables and Columns do not respond to filter and slicer selections in reports. See my post on this topic here: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

Then you can create a measure that you can either use as a filter in each visual, or multiply by all other measures to filter the visuals: 

Year More Filter =

VAR _Year = SELECTEDVALUE('Year'[Year])

RETURN

IF( _Year > [Target Year Value], 1)

Filtered Year Less Total Value = [Total Value]*[Year Less Filter]

See attached pbix below signature for demo.

[AlexisOlson]

A calculated table cannot be responsive to slicer selections since they are only evaluated when your data model is loaded or refreshed, not whenever a slicer or a filter is interacted with.

[Weijia]

Thanks for the prompt reply. If I want to dynamically split a table into two, is there a way to do it?

[AlexisOlson]

You can define measures to behave differently for one side or the other of a split which you can use to implicitly (return blank for one side) or explicitly filter (via a measure filter) your visuals.