cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Weijia
New Member

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

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])
 
Weijia_0-1634571233445.png

 

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 
1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

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

AllisonKennedy_0-1634592398416.png

Filtered Year Less Total Value = [Total Value]*[Year Less Filter]
 
See attached pbix below signature for demo.

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
AllisonKennedy
Super User
Super User

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

AllisonKennedy_0-1634592398416.png

Filtered Year Less Total Value = [Total Value]*[Year Less Filter]
 
See attached pbix below signature for demo.

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

AlexisOlson
Super User
Super User

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.

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

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.