Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI Paginated Report parameters with (Null) values

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 parameter...
Reports
  • d_gosbell's avatar
    d_gosbell
    4 years ago

    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 )

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    d_gosbell
    Icon for Super User rankSuper User
    4 years ago
    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 & """)" ) &

    ")"