Power BI Paginated Report parameters with (Null) values

[Anonymous]

Hi,  Would like to ask for some help with the Paginated report parameters. We have one power bi paginated report for which  datsource is power bi dataset. Requirement here is to make report parameters as optional , We have selected Allow null value as YES under parameter properties so in that case user doesn't need to select parameter value, by defaul (null) will be selected for the parameter. so requirement here is,  with (null) value selection all the underlying value of parameter should be pass in the report DAX query. To make the scenerio more clear, here is the example . Suppose I have one report parameter named as EnteredCurrency and EnteredCurrency parameter having three values (EUR,GBP,USD) so if by default (null) is passing for EnteredCurrency parameter then EUR,GBP,USD should be pass in the underlying report DAX query. 

Report DAX Query is :

EVALUATE SUMMARIZECOLUMNS

(

'ART REP048 UKGAAP'[Accounting_Date],

 'ART REP048 UKGAAP'[Entered_Currency],

 'ART REP048 UKGAAP'[Accounting_Period],

FILTER(VALUES('ART REP048 UKGAAP'[Entered_Currency]), ('ART REP048 UKGAAP'[Entered_Currency] = @EnteredCurrency))

)

[Anonymous]

Hi @d_gosbell ,

Thanks for the prompt response. 

I have tried both the solution provided by you and seems second approach is working. I have done it for one parameter initially to test the logic that worked but when I applied the same thing on original report with all the parameters then report is running for so long and then finally getting the error. We have total 9 parameters. Can you pls suggest something in this regard ?

[d_gosbell]

---

@Anonymous wrote:

when I applied the same thing on original report with all the parameters then report is running for so long and then finally getting the error. We have total 9 parameters. Can you pls suggest something in this regard ?

---

So the other approach that I've used is a lot more complex, but it basically involves only using report level parameters and not passing any of them to the dataset or query then constructing your query as an expression by clicking the little "fx" button next to the query text in the dataset properties.

Then you build your query up as a vba expression something like the following. It gets tricky trying to get the quoting correct, but it does work. I have a report with 16 parameters where the users wanted to also use * as a wildcard so BL* will search for values starting with "BL" and I do this using this technique.

="EVALUATE SUMMARIZECOLUMNS

(

'ART REP048 UKGAAP'[Accounting_Date],

 'ART REP048 UKGAAP'[Entered_Currency],

 'ART REP048 UKGAAP'[Accounting_Period],
" & iif( Parameters!EnteredCurrency.Value <> "<ALL>", "

FILTER(VALUES('ART REP048 UKGAAP'[Entered_Currency]), 'ART REP048 UKGAAP'[Entered_Currency] = """ & Parameters!EnteredCurrency.Value & """)" ) &

")"

[d_gosbell · Accepted solution]

So if you want to handle the scenario where they either pick on currency or by default if the parameter is null they get all of them then you could use the COALESCE function. What coalesce does is to return the first non-null value, so if the parameter is null it will return the value of the parameter itself

EVALUATE SUMMARIZECOLUMNS

(

'ART REP048 UKGAAP'[Accounting_Date],

 'ART REP048 UKGAAP'[Entered_Currency],

 'ART REP048 UKGAAP'[Accounting_Period],

FILTER(VALUES('ART REP048 UKGAAP'[Entered_Currency]), ('ART REP048 UKGAAP'[Entered_Currency] = COALESCE( @EnteredCurrency, 'ART REP048 UKGAAP'[Entered_Currency] )))

)

A similar alternative if you want to use a dropdown list in your parameter is to union on a value like "<ALL>" in the query for you parameter values then use an IF to check for this.

FILTER(VALUES('ART REP048 UKGAAP'[Entered_Currency]), ('ART REP048 UKGAAP'[Entered_Currency] = IF( @EnteredCurrency = "<ALL>", 'ART REP048 UKGAAP'[Entered_Currency], @EnteredCurrency )