Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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]

Need Help

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    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]

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago

        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?