Create dynamic reports for multiple values? (Microsoft Training Clarification)

[rodneyc8063]

So Im going through the Microsoft Training for DA100 and am a bit confused by something in one of the modules.

Specifically on this page in the section "Create dynamic reports for multiple values"

https://docs.microsoft.com/en-us/learn/modules/manage-datasets-power-bi/2-report-parameters

Now this entire page is talking about parameters and how to use parameters. 

So going through the given example it says to "Right click query 1 and select [Create function]" - A new window will pop up asking for a name for the function as shown below

So far this is easy, I just gave a name to a function. The [Parameters: SalesPerson] if I understand correctly was the parameter that was created earlier on in the example and is being used in the query. Cool beans so far

After clicking OK it should then pop up the following screen

So here I get a bit confused. I dont understand why suddenly it would show [GetSalesPerson (1)] with another subfolder [GetSalesPerson(3)]? 

Moving along - The example goes on to say to go to the "Sheet1" query (which is now renamed as SalesPersonID supposedly) and "Invoke Custom Function" which then yields the following screen

So this is where I get quite lost......

So there is a drop down "Function Query" - I choose the function that was created earlier "GetSalesPerson", which will then auto-populate the "New column name"

I see the previously defined parameter "SalesPerson" - Ok - And another drop down listing "SalesPersonID"

Im not sure what happened here - As when I think function, I think PL/SQL functions/procedures where there is code written. Based on this example, no code was written - So Im not sure exactly whats going on?

Also, Im not sure how realistic this example would be - To have someone input a list of excel values in order to pull data in. Does anyone have any better examples of when to use "Invoke a custom function"?

[MFelix · Accepted solution]

Hi @rodneyc8063 ,

The custom function uses the columns of the table were you invoke it to populate the parameters of the function.

In this case what you see is that they create a new Sheet 1 where the columns are the SalespersonID

Then they use the values on this table to call the custom function.

So in summary the SalesPersonId you see on the image of the custom function is the column name on the table where you are calling the function.

In fact having an SQL Query on the power query if you replace the filtering on the SQL syntax by a parameter you can in fact use an input from the user (excel or other type) to make the SQL return a different filter from the ones you created the query, this is the same behaviour has you have in any SQL consultation that you can define variable to filter your information the only thing is that Power BI allows you to add the values of those parameters in several different ways using the custom function.

Regarding the image of the folders is just one way or organizing the information if you right click a query you can place it within a folder.

[rodneyc8063]

Hi @MFelix 

Hm - I just wanted to clarify then, Im assuming then that a custom function is not simply limited to one parameter or one column input? Multiple parameters can be used with multiple column inputs?

Thank you for clarifying so far though, that does make more sense 🙂

[MFelix]

Correct.

If you look at the syntax of a custom function you will have something similar to:

let
    Source = (Parameter1 as text) => let
        Source = ...,
        Step1 = ... Parameter1,
        Step2 = ...
    in
        Step2
in
    Source

In the values in parentesis you can setup something similar to this:

let
    Source = (Parameter1 as text, Parameter2 as number) => let
        Source = ...,
        Step1 = ... Parameter1,
        Step2 = ... Parameter2
    in
        Step2
in
    Source

This way you will have has many parameter has you need and you can place them on the steps where you need them.

[rodneyc8063]

Many thanks @MFelix !