Paginated Reports "All" parameter

[RBunting]

I have connected Paginated Reports to a dataset that I have previously published in Power BI. I'd like to have a parameter to limit the results by Manufacturing Plant. But I'd also like to be able to return all data.

In SSRS, I used to append a row into the parameter's available values query with the value "All" and then, in the sql, check for the "ALL" value and, if detected, process the query with no filter on that field. I've tried to do the same thing in Paginated Reports.

Howver, I can't seem to append my 'All' or 'NoFilter' row. The dax works in DAX stuidio, but Paginated Reports isn't processing it. Can anyone offer some advise? Here is the Dax that I'd like to use to cause the aparemter to provide a list of available Plants and also a row for "All," or "No Filter."

EVALUATE
 (
    UNION (
        DATATABLE (
            "Plant Number", STRING,
            "Plant Name", STRING,
            {
                { "ALL", "No Filter" }
            }
        ),
        SUMMARIZE ( 'PLANT PR1', 'PLANT PR1'[Plant Number], 'PLANT PR1'[Plant Name] )
    )
)

[RuchikaSoni]

Thanks very much, @pdacheva . The query worked! Appreciate all the help you extended.

[mvabalaitis]

@RBunting - Thanks so much for all the info, super helpful in converting our SSRS to Paginated. One question regarding the addition of the "All" value. I was able to get the union working in my dataset, however when I attempt to add the ORDER BY functionality the expression breaks throwing the following error: "A single value column cannot be determined". The issue is resolved by removing the union, which gets me back to square one.

Has anyone ran into this issue and can offer any advice?

[pdacheva]

Hi @RBunting , I have the same business case - I already have my Parameter values with All., but now the problem is with the Dataset query.

How d you manage to filter - is All - Show All, otherwise the selected value.

I'm using this condition RSCustomDaxFilter(@Measure,EqualToCondition,[v_MeasureDetail_Latest].[MeasureCode],String) in the main dataset query. but here I can't add any OR condition. 

I tried to do it in the expression of the Parameter itself like:

IIF(@Measure = "All", Nothing, Parameters!Measure.Value) or IIF(@Measure = "All", True, Parameters!Measure.Value), but no luck there either, I get error for passing invalid value to the parameter.

How did you apply the parameter to your dataset afterwards?

Thanks!

[RBunting]

pdacheva,

My entire query is a dynamic expression that generates a valid DAX statement.

During the evaluation of the query text, I check the parameters and build the specific query language to send to the source system.

="EVALUATE(FILTER(...  " + iif( Parameters!Plant.Value <> "ALL", "&& 'PLANT PR1'[Plant Number] = """ & Parameters!Plant.Value & """", "") + "  ...

so, if the parameter is not set to "all," this line in the query becomes a piece of the filter conditions "&& 'PLANT PR1'[Plant Number] = "99999999".  But if the plant number IS set to "all" then this whole bit just gets omitted from the DAX query and no filter on Plant is ever applied.

[pdacheva]

Hi RBunting,

Thank you for your reply!

Sorry for not replying earlier - I changed the filtering of this dimension within the DAX (not using the RSCustomDaxFilter formula) and it worked as expected.

Best,

[Anonymous]

Hi @pdacheva  @RBunting 

   Can you please share me the format of the filter in DAX? I tried but getting error.

Thanks

[pdacheva]

Hi @Anonymous ,

Make sure you add the filter at the end of the SUMMARIZE or SUMMARIZECOLUMNS. If you share the error message maybe the community could help better. See below how I am filtering my DAX:

[V_20]

Hi @RBunting @Craig_Donoghue @RuchikaSoni @pdacheva @mvabalaitis 

I am running into the same requirement. I could not use RsCustomdaxfilter and filter dax function in the same query. The query is not picking up the parameter. 
would you let me know how you used both like in the image shared above? 

[RuchikaSoni]

 This is how I used it. All my parameters are defaulting to "ALL".

FILTER('TreatyMaster_BI',(@UA= " ALL" || PATHCONTAINS (@UA ,'TreatyMaster_BI'[Underwriting Assistant]))),
RSCustomDaxFilter(@ConversionTypeCurrencyConversion,EqualToCondition,[Conversion Type].[Currency Conversion],String), 
FILTER(VALUES('Transaction Dates'[Date]), (vFromTransactionDatesDate1ALL || 'Transaction Dates'[Date] >= DATEVALUE(vFromTransactionDatesDate1) + TIMEVALUE(vFromTransactionDatesDate1)) && (vToTransactionDatesDate1ALL || 'Transaction Dates'[Date] <= DATEVALUE(vToTransactionDatesDate1) + TIMEVALUE(vToTransactionDatesDate1))), "Written Premium", [Written Premium], "Earned Premium", [Earned Premium], "Total Paid Loss", [Total Paid Loss], "Additional Case Reserve", 

[RuchikaSoni]

Hello,

Your post was very helpful. However the filter condition fails when multiple values are selected in report parameter.

Here is what I am using: The report execution fails when I supply multiple values on Program Name parameter. Any tips @pdacheva 

FILTER('TreatyMaster_BI',(@TreatyMasterBIProgramName= " ALL" || 'TreatyMaster_BI'[Program Name] = @TreatyMasterBIProgramName)),

[pdacheva]

Hi @RuchikaSoni ,

Yes, you cannot use "=" for a multiselect parameter. Set it up like this:

FILTER('TreatyMaster_BI',(@TreatyMasterBIProgramName= " ALL" ||  PATHCONTAINS(@TreatyMasterBIProgramName,'TreatyMaster_BI'[Program Name]))

And then, in your dataset's parameters setting, set the @TreatyMasterBIProgramName as

=JOIN(Parameters!TreatyMasterBIProgramName.Value,"|")

This should work.

Cheers,

[Craig_Donoghue]

Amazing. I've been trying to figure this out for two days. Thanks!

[RuchikaSoni]

Thanks very much @pdacheva . The solution worked.

[Anonymous]

Hi @pdacheva 

   Thanks. "All" is working. If i set All as default then working good and if i add available values from query then All is not picking up as a default. Any idea? Can you share your email?

[pdacheva]

Well, in this case you need to make sure you have "All" in the available values of your parameter query. Just do a UNION and add dummy record "All" in the available values of your parameter list.

[RuchikaSoni]

Hello @pdacheva ,

I am currently using sql logic to append "ALL" in the parameter list. The logic is working fine but since I have a lot of parameters on my paginated report I feel the report is taking longer to load initally. 

Can you suggest How to union "ALL" using DAX?

Logic to append "all" in parameters using sql is as follows:

select DISTINCT [Reporting SBU]
from TreatyMaster_BI
WHERE (([Company] in (@TreatyMasterBICompany)) OR ' ALL' IN (@TreatyMasterBICompany))
and [Reporting SBU] is not null
UNION
SELECT ' ALL' as 'Reporting SBU'
ORDER BY 'Reporting SBU'

[pdacheva]

Hi @RuchikaSoni ,

It should be something like that. Just check the syntax.

// DAX Query
EVALUATE
UNION(
SUMMARIZE(
FILTER(TreatyMaster_BI, (PATHCONTAINS(@TreatyMasterBICompany,'TreatyMaster_BI'[Company]) || @TreatyMasterBICompany = "All") && NOT(ISBLANK('TreatyMaster_BI'[[Reporting SBU]])) )
TreatyMaster_BI[Reporting SBU])
, DATATABLE (
"Reporting SBU", STRING,
{
{" All"}
}
)
)

[d_gosbell]

What error message are you getting? I just tried doing the same thing against a copy of the Adventure Works database with the following query and I was able to create a dataset in my report with no problems.

EVALUATE
 (
    UNION (
        DATATABLE (
            "Country Region Code", STRING,
            "Country Region Name", STRING,
            {
                { "ALL", "No Filter" }
            }
        ),
        SUMMARIZE ( Geography, Geography[Country Region Code], Geography[Country Region Name] )
    )
)

[RBunting · Accepted solution]

Blast.  It was a picky little syntax error.

EVALUATE
 ( ...

The command shown above is no good.  Report Builder does not like that white space between the EVALUATE keyword and the open pren.  The following variation worked fine.

EVALUATE( ...

Thanks for taking the time to look into the problem.  

[d_gosbell]

The Report Builder Query editor does it's own parsing and sometimes it can't handle things which are actually valid syntax. In terms of pure DAX syntax the whitespace/linebreaks are not significant, plus EVALUATE is a keyword not a function so you don't even need the ( ) you can just do EVALUATE UNION(...)

Or the other thing that could have gone wrong is that are at least 2 different types of space characters. The unicode character set has both a normal space and a non-breaking space (which affects where soft line breaks can appear). Report Builder does not work properly with non-breaking spaces, but you typically only get these when copying from web pages or other documents.

Note that when you paste into DAX Studio these characters get replaced with "standard" spaces so sometimes pasting into DAX Studio then re-copying and pasting the same query back into Report Builder will fix these weird "syntax" errors.