Forum Discussion

An0n's avatar
An0n
Frequent Visitor
3 years ago

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

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 chang...
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

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's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

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's avatar
    An0n
    Frequent Visitor
    3 years ago

    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.