[SOLVED] Add an "ALL" item to list of items in Parameter drop down

Add an "ALL" item to list of items in Parameter drop down

I am experimenting in PowerBI Paginated Reports and find the experience to be nearly identical to SSRS development.

So I'm bringing back a dataset borrowed from an existing Power BI solution. I'd like to build a parameter to filter the results. So I create some DAX to read just the unique combinations of two fields from the dataset for value and display.

I'd like to replicate an old trick where in SQL I would just union on a row with fields like "All" or "No Filter". While I'm able to construct this analog in DAX Studio, it doesn't seem to be migrating well to Paginated reports.

EVALUATE
 (
    UNION (
        ROW ( "Plant Number", "ALL", "Plant Name", "All Plants" ),
        SUMMARIZE ( 'PLANT PR1', 'PLANT PR1'[Plant Number], 'PLANT PR1'[Plant Name] )
    )
)

That's the DAX that I can use in DAX studio, but when I put it in Paginated Reports as a data source for my 'Plant' Parameter, I get a bunch of nulls in the dropdown. Any Ideas on how I could improve this to work in Reports?

Solution

Circling back to this as it turned out to be just a very picky little problem with Report Builder's parsing. I did replace SUMMARIZE with ALL as it seemed more appropriate for what I was doing, but the problem I was having actually had to do with the white space that was inserted by DAX Studio's 'format DAX' function. Report Builder does not like that whitespace between the EVALUATE keyword and initial opening parentheses. Once I snugged up the open paren to the end of EVALUATE everything worked fine.

EVALUATE(
    UNION (
        ROW ( "Plant Number", "All", "Plant Name", "<All Plants>" ),
        ALL ( 'PLANT PR1'[Plant Number], 'PLANT PR1'[Plant Name] )
    )
)
ORDER BY [Plant Name]