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 fi...
Need Help
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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