Using a Parameter in the dataset sql where clause

Power BI Paginated Reports
1

I have created a parameter with 2 default values (YES NO) that the user can select only one of them. This parameter DOES NOT match any value in my dataset table. What I want to use this for is selecting only data records with On Hand balance records when they select the YES and select all records when they select NO. How do I use this parameter with in dataset query for 2 different tests for the On Hand either <> 0 or exclude the On Hand test al together? Image shows my parameter with the YES NO.

image
image979×144 2.53 KB

I need to use this parameter in my query, testing to see if they selected YES, then include the test for On Hand ,. 0. Else leave out this test. second image shows my query.
image
Thank you
Jim

2

Hi @jrubino77 - As you are looking to pass value to a SQL query based on a Slicer, then you first needs to bind the Slicer to a Power Query parameter. Also, it is possible only in Direct query and not in Import mode.

For information on this, refer to below Blog post

If want to create a Parameter in Power Query, then need to write two Source sql statements inside a single query and call the correct Source based on the Parameter value using IF statement. For this, can refer to below

https://radacad.com/change-the-data-source-type-of-the-power-bi-dynamically-using-power-query-parameters#:~:text=You%20can%20use%20Power%20Query,%2C%20or%20even%20re-publish.

Thanks
Ankit J

3

Ankit,

I am not trying to pass value from a slicer. I want to use a value from the Parameter selected in my SQL where clause. I do not have a table column to compare to this value. Example table.yesnofield = @Parm_YesNo ← I cannot do this. I want to do something like comparing the @Parm_YesNo = “Yes” then do more table column tests. OR if @Parm_YesNo = “No” then do these table column tests.

If you understand is this possible?
Thank you,
Jim

4

Hi @jrubino77 - Ignore my previous comment. I thought it is related to normal Power BI reports but now realized it is related to Paginated reports that I am not much aware of.

Thanks
Ankit J

5

Hi @jrubino77

This works in SQL, don’t see why it wouldn’t in paginated report:

CASE
    WHEN @OnHand = 'Yes'
        THEN OnhandBalance
    ELSE 1
END <> 0

If the user selects yes, the actual Onhand Balance column is brought back, and it will only those those where it is not 0. If yes isn’t selected, the case statement will bring back 1, which is not 0, so will bring back all records.

1 Like
6

Hi @jrubino77, we have just released Sue Bayes’ Paginated Report Writer course. Have you checked if this scenario has been discussed in one of the videos?

Thanks

7

Yes I have gone through that course. I need more insight on how to use Parameters that I create within SQL. I am hoping her part 2 will show that.
Thank you,
Jim

1 Like
8

Hi @jrubino77! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!