Changing a filter across multiple tabs based on predefined values rolled forward

[An0n]

Hi All,

In a Power BI file I currently import on the one hand a folder which contains Excel files and on the other hand one Excel file directly. 

The names of the Excel files in the folder change from one quarter to the next based on the quarter and the year e.g. Product 1_Country A_Q4 22 would change to Product 1_Country A_Q1 23 the following quarter. Each product and country combination has a seperate tab in the Power BI file after which several measures and filters are implemented consistently across each tab. The name of the Excel files is also present as a filter in each tab. How could I roll forward all tabs to the new quarter while avoiding having to specifically change to the new name of the file in the filter on each tab?

Kind regards and Thanks in advance!

[Sahir_Maharaj]

Additionally, you could also consider using a dynamic parameter in the filter instead of hardcoding the Excel file name. By using a dynamic parameter, you only need to change the value of the parameter to roll forward all tabs to the new quarter, without having to change the filter on each tab.

[Sahir_Maharaj]

This can be done by creating a new parameter table in your Power BI data model and referencing it in your filter. The parameter table would contain the current quarter and year, and the name of the Excel file could be constructed dynamically based on this information. You can then use DAX formulas to calculate the name of the Excel file based on the parameter values, and use that in your filter instead of hardcoding the name of the file.

[An0n]

Thank you @Sahir_Maharaj for the suggestions.
To clarify there are multiple tabs (30+) that have a filter which needs to be changed for example:

On tab 1: Product 1_Country A_Q4 22 needs to be changed to Product 1_Country A_Q1 23

On tab 7: Product 3_Country C_Q4 22 needs to be changed to Product 3_Country C_Q1 23

On tab 23: Product 12_Country B_Q4 22 needs to be changed to Product 12_Country B_Q1 23

How can I do this efficiently? As I understand it currently with your solution I would still need to update each parameter for the tabs seperately.

[Sahir_Maharaj]

Hello @An0n,

One way to solve this issue is to use a parameter in Power BI Desktop. You can create a parameter that will allow you to dynamically change the name of the Excel file in the filter, without having to manually update the filter on each tab.

[Sahir_Maharaj]

Here are the steps to create a parameter in Power BI Desktop:

[Sahir_Maharaj]

This will allow you to easily roll forward all tabs to the new quarter, without having to specifically change the name of the file in the filter on each tab.

[Sahir_Maharaj]

8. Now, when you view the report in the Power BI Service, the filter will dynamically update to the new file name, based on the value of the parameter. You can change the value of the parameter as often as you need, without having to manually update the filter on each tab.

[Sahir_Maharaj]

7. Save and close the "Manage parameters" dialog box.

[Sahir_Maharaj]

6. In the "Manage parameters" dialog box, update the value of the "File Name" parameter to the new file name (e.g. "Product 1_Country A_Q1 23").

[Sahir_Maharaj]

5. In the Power BI Service, go to the "Report" section and click on "Manage parameters".

[Sahir_Maharaj]

4. Publish the report to the Power BI Service.

[Sahir_Maharaj]

3. In the "File Name" filter, replace the hardcoded file name with the parameter name (e.g. "File Name").

[Sahir_Maharaj]

2. In the "Create Parameter" dialog box, give the parameter a name (e.g. "File Name"), select "Text" as the data type, and enter the current file name in the "Default Value" field (e.g. "Product 1_Country A_Q4 22").

[Sahir_Maharaj]

1. In Power BI Desktop, click on the "Modeling" tab and then click on "New Parameter".