Forum Discussion

JimJim's avatar
JimJim
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Paginated Report - cascading parameters

Hi Guys,

 

I have a dataset for my Region parameter, defined as follows:

 

EVALUATE VALUES ('Business Hierarchy'[Region])

 

I also have a dataset for my Area parameter and failing to filter based on the Region selection. I am trying the below:

 

EVALUATE SUMMARIZECOLUMNS ( 'Business Hierarchy'[Area], FILTER ( 'Business Hierarchy', [Business Hierarchy].[Region] IN region ) )

 

But getting error "The function expects a table expression for argument '2', but a string or numeric expression was used."... How do I change region to a table type?

 

Edit: The following DAX works when a single value in Region is selected, but doesn't work when multiple values are selected

 

EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS ( 'Business Hierarchy'[Area]),
'Business Hierarchy'[Region]=@Region)

Need Help
  • JimJim's avatar
    JimJim
    2 years ago

    I worked it out, adding the solution here in case anyone else needs it.

     

    The query for your dataset that your param uses should be like this:

     

    DEFINE
    VAR r =
    FILTER (
    VALUES ( 'Business Hierarchy'[Region] ),
    PATHCONTAINS ( region, 'Business Hierarchy'[Region] ) )

    EVALUATE
    SUMMARIZECOLUMNS ( 'Business Hierarchy'[Area], r )

     

    Then in the parameters section, for parameter value choose the expression and use the following format:

     

    =Join(Parameters!MyParameter.Value,"|")

2 Replies

  • JimJim's avatar
    JimJim
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    I worked it out, adding the solution here in case anyone else needs it.

     

    The query for your dataset that your param uses should be like this:

     

    DEFINE
    VAR r =
    FILTER (
    VALUES ( 'Business Hierarchy'[Region] ),
    PATHCONTAINS ( region, 'Business Hierarchy'[Region] ) )

    EVALUATE
    SUMMARIZECOLUMNS ( 'Business Hierarchy'[Area], r )

     

    Then in the parameters section, for parameter value choose the expression and use the following format:

     

    =Join(Parameters!MyParameter.Value,"|")

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    JimJim 

    hello, 

    in summarizecolumns you should use the full expression to filter data 

     

     

    EVALUATE
    CALCULATETABLE(
            SUMMARIZECOLUMNS ( 'Business Hierarchy'[Area]),
            filter(
                 all('Business Hierarchy'),

                 Business Hierarchy'[Region]=@Region

             )

    ) 

     

     

     

     

    If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍