Forum Discussion

rodneyc8063_1's avatar
rodneyc8063_1
Helper V
5 years ago
Solved

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

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...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi rodneyc8063_1 ,

     

    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.

     

     
     
     
     
     

     

MFelix's avatar
MFelix
Super User
5 years ago

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.