Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

filter values from parameter in report builder from hidden dataset query

Dear all,

 

i have a report builder report with a parameter, when i add the parameter a hidden dataset is added with the query below. 

i would like to filter the results in my parameter with an filter. how can i add the filter to this query?

A filter in the same table as table[dimension] field, which says if the dimension value is active. so the fieldname is table[dimensionactive]. I only want the active dimensions so table[dimensionactive] = 1.

 

can someone help me adjust the below query where this is filtered?

 

 

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL ('table'[dimension], "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, "" & 'table'[dimension], "Blank()"), "ParameterValue", "" & 'table'[dimension], "ParameterLevel", [ParameterLevel], "'table'[dimension]", 'table'[dimension]) order by 'table'[dimension], [ParameterLevel]

5 REPLIES 5
SteveSchouten
Frequent Visitor

Same issue here, some more advice maybe? 

 

AllisonKennedy
Super User
Super User

@Anonymous  Not entirely sure I understand you correctly, but does this work?

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL (FILTER('table'[dimension],   table[dimensionactive] = 1) , "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, "" & 'table'[dimension], "Blank()"), "ParameterValue", "" & 'table'[dimension], "ParameterLevel", [ParameterLevel], "'table'[dimension]", 'table'[dimension]) order by 'table'[dimension], [ParameterLevel]


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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

Anonymous
Not applicable

hi @AllisonKennedy 

 

thanks for the answer, after i tried your potential solution, i get the following error message when i try to run the report:

Query (1, 124) The ROLLUPADDISSUBTOTAL function only accepts a fully qualified column reference or ROLLUPGROUP function as the argument number 3.

@Anonymous  I'm not sure but I think that might be due to missing brackets or other syntax error. Also, just looking at it now, I think I've got another error in there, the FILTER function just needs the table first: 

 

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL (FILTER('table',   table[dimensionactive] = 1) , "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, "" & 'table'[dimension], "Blank()"), "ParameterValue", "" & 'table'[dimension], "ParameterLevel", [ParameterLevel], "'table'[dimension]", 'table'[dimension]) order by 'table'[dimension], [ParameterLevel]

 

If that doesn't work can you please provide sample file via onedrive?


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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

Anonymous
Not applicable

1hi I still get the following error:

Query (1, 129) The ROLLUPADDISSUBTOTAL function only accepts a fully qualified column reference or ROLLUPGROUP function as the argument number 3.

 

i added a link to my report here, its in the hidden data set, there is the query which can be filtered. Dont know if it works with the connection to the data set, but there is another link for the powerbi desktop file which is used as a data source.

 

paginated report:

https://1drv.ms/u/s!AsL7kXdC7lqjjBxWG4VTxMpgCSos?e=UIWFTd

Data model power bi desktop:

https://1drv.ms/u/s!AsL7kXdC7lqjjB0vzu2vBvkAYVYN?e=rDBxJE

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.