Create dynamic reports for multiple values

 To accommodate multiple values at a time, you first need to create a Microsoft Excel worksheet that has a table consisting of one column that contains the list of values.

Next, use the Get data feature in Power BI Desktop to connect to the data in that Excel worksheet, and then follow these steps:

  1. On the Navigator window, select Edit to open the data in Power Query Editor, where you see a new query for the data table.

  2. Rename the column in the table to something more descriptive.

  3. Change the column data type to Text so that it matches the parameter type and you avoid data conversion problems.

  4. In the query Properties section, change the name of the data source to something more descriptive. For this example, enter SalesPersonID.

Next, you need to create a function that passes the new SalesPersonID query into Query1:

  1. Right-click Query1 and then select Create function.

  2. Enter a name for the function and then select OK. Your new function appears in the Queries pane.

  3. To ensure that Query1 doesn't show up in the field list for the report, which could potentially confuse users, you can disable it from loading in the report by right-clicking Query1 again and then selecting Enable load (selected by default) to disable the feature.

  4. Select the SalesPersonID query that you loaded from the Excel worksheet and then, on the Add Column tab, select Invoke custom function to run the custom function that you created.

  5. On the Invoke Custom Function window, select your function from the Function query list.

The New column name updates automatically and the table that contains the values that you're going to pass through the parameter is selected by default.

  1. Select OK and, if necessary, run the native query. A new column for your GetSalesFromSalesPerson function appears next to the SalesPersonID column.

  2. Select the two arrows icon in the new column header and then select the check boxes of the columns that you want to load. This section is where you determine the details that are available in the report for each value (sales person ID).

  3. Clear the Use original column name as prefix check box at the bottom of the screen because you don't need to see a prefix with the column names in the report.

  4. Select OK. You should be able to view the data for the columns that you selected, for each value (sales person ID). If necessary, you can add more values (sales people IDs) to the SalesPersonID column in the Excel worksheet, or you can change the existing values.

  5. Save your changes and then return to Power Query Editor.

  6. On the Home tab, select Refresh Preview, and then run the native query again (if necessary). You should see the sales from the new sales people IDs that you added into the worksheet.

  7. Select Close and Apply to return to the report editor, where you see the new column names in the Fields pane.

    8. Now, you can start building your report.


Comments

Post a Comment

Popular posts from this blog

Azure built-in roles for tables

Azure RBAC provides built-in roles for authorizing access to table data using Microsoft Entra ID and OAuth. Built-in roles that provide permissions to tables in Azure Storage include: Storage Table Data Contributor: Use to grant read/write/delete permissions to Table storage resources. Storage Table Data Reader: Use to grant read-only permissions to Table storage resources. To learn how to assign an Azure built-in role to a security principal, see Assign an Azure role for access to table data. To learn how to list Azure RBAC roles and their permissions, see List Azure role definitions. For more information about how built-in roles are defined for Azure Storage, see Understand role definitions. For information about creating Azure custom roles, see Azure custom roles. Only roles explicitly defined for data access permit a security principal to access table data. Built-in roles such as Owner, Contributor, and Storage Account Contributor permit a security principal to manage a storage acc...
Read more

Explore Dataflows Gen2 in Microsoft Fabric

  In Microsoft Fabric, you can create a Dataflow Gen2 in the Data Factory workload or Power BI workspace, or directly in the lakehouse. Since our scenario is focused on data ingestion, let's look at the  Data Factory  workload experience. Dataflows Gen2 use Power Query Online to visualize transformations.  1. Power Query ribbon Dataflows Gen2 support a wide variety of data source connectors. Common sources include cloud and on-premises relational databases, Excel or flat files, SharePoint, SalesForce, Spark, and Fabric lakehouses. Then there are numerous data transformations possible, such as: Filter and Sort rows Pivot and Unpivot Merge and Append queries Split and Conditional split Replace values and Remove duplicates Add, Rename, Reorder, or Delete columns Rank and Percentage calculator Choose Top N and Bottom N You can also create and manage data source connections, manage parameters, and configure the default data destination in this ribbon. 2. Queries pane The ...
Read more

Select and configure an appropriate method for access to Azure Blobs

Azure Storage supports using Microsoft Entra ID to authorize requests to blob data. With Microsoft Entra ID, you can use Azure role-based access control (Azure RBAC) to grant permissions to a security principal, which may be a user, group, or application service principal. The security principal is authenticated by Microsoft Entra ID to return an OAuth 2.0 token. The token can then be used to authorize a request against the Blob service. Authorization with Microsoft Entra ID is available for all general-purpose and Blob storage accounts in all public regions and national clouds. Only storage accounts created with the Azure Resource Manager deployment model support Microsoft Entra authorization. For optimal security, Microsoft recommends using Microsoft Entra ID with managed identities to authorize requests against blob, queue, and table data, whenever possible. Authorization with Microsoft Entra ID and managed identities provides superior security and ease of use over Shared Key author...
Read more