Capture and pass slicer values to paginated reports

[MattKarriker2]

I am looking for help to capture slicer values so I can properly pass to paginated reports.  I have a solution that a user can call a paginated report to give more details inside the report.  This is a very large data model and some dimension tables have over 3M distinct values.  This causes an issue with my paginated report URL so rather than passing all of the incldued values I want to determine if the exclude is shorter and if so pass the exclude list rather than the include list.  

The include list is simple enough using concatenatex function. 

CONCATENATEX(VALUES('Product'[Level3]),'Product'[Level3],""",""")        

I also have a sample where I can capture excluded values but it only works when levels higher in the hierarchy are selcected but not when lower level values are selected.  The below example works if Levels 1,2, or 3 are filtered.  However the calculation returns blank when Level4 is filtered.  
Calculate(CONCATENATEX(Distinct('Product'[Level3]),'Product'[Level3],""","""),
    EXCEPT(
        Distinct(selectcolumns(ALLEXCEPT ('Product','Product'[Level1],'Product'[Level2],),"Level3",'Product'[Level3])),
        ALLSELECTED( 'Product'[Level3] )))

Any thouoghts on what is causing this?  I need to be able to capture the exclusion list from each level of the hierarchy to make this work.  

[Anonymous]

Hi @MattKarriker2 

Has your problem been solved ? If no, please provide your sample and the result you want .

If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[AlexisOlson]

The ALLSELECTED function is tricky, so I'm guessing that's where things are going awry. In particular, if you only filter Level4, then Level3 is cross-filtered but not directly filtered and ALLSELECTED doesn't apply that cross-filtering.

Even if this isn't exactly the problem, I'd advise writing it a simpler version:

ExcludeList =
VAR SelectedValues = VALUES ( 'Product'[Level3] )
RETURN
    CALCULATE (
        CONCATENATEX ( DISTINCT ( 'Product'[Level3] ), 'Product'[Level3], """,""" ),
        FILTER ( ALL ( 'Product'[Level3] ), NOT ( 'Product'[Level3] IN SelectedValues ) )
    )