Paginated Report - cascading parameters

[JimJim]

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)

[Daniel29195]

@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 🫡👍

[JimJim · Accepted solution]

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,"|")