Forum Discussion

adrianal's avatar
adrianal
Regular Visitor
5 years ago
Solved

Change data source based on filters

Hy! Is it possible to change data source based on a date filter?

 

For example: i have created a dashboard based on a data set for let's say August data (which i read from Share Point), and i want to schedule it to refresh every month automatically, but i want users to be able to see the older datasest as well. So if i have a date filter and i chose July, it will refresh and read the data set for July and display it in the dashboard and so on... First I was thinking to combine the files, and to add new data to the same file every month, but after 3-4 months the file will be too big, and the loading process will take too much time, so i was thinking that maybe there's a way to add a date filter in the dashboard, and read the files that were created on the month that i set the filter on.

 

Thanks! 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, adrianal 

     

    You may try using query parameter to filter data, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column as below.

    Date.Year([Date])*100+Date.Month([Date])

     

     

    Then you may go to 'Home'=>'Manage Parameters', create a new parameter as below.

     

    Then you need to use the parameter to filter the result.

     

    Finally you may modify the parameter in power bi desktop to get the corresponding result.

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi adrianal ,

     

    This is possible to some degree, but it would require significant gymnastics. I think you would need to structure your Power Query queries in such a way that your history tables were named as relative months, rather than absolute month names. What I mean by this is, we are in September now, so your August data table would need to be called 'monthMinus1' or similar, and the actual query that populated it would need to recognise current date, and use this information to load from your August source. You would do the same for 'monthMinus2', monthMinus3' tables etc. This is heavily dependent on how your data comes into Power Query i.e. what the file names are and their attributes viewable by Power Query.

    Once the separate tables have been loaded into the model, then you would need a calendar table with a relative month field that you could reference in measures to switch your outputs. This would look something like this:

     

    monthSales =
SWITCH(
    TRUE(),
    SELECTEDVALUE(Calendar[RelativeMonth]) = "-1", SUM(monthMinus1[Sales]),
    SELECTEDVALUE(Calendar[RelativeMonth]) = "-2", SUM(monthMinus2[Sales])
)

     

     

    However, this is all purely theoretical. I've never tried this IRL and would have absolutely no desire to, to be honest.

     

    Another option entirely may be to only display current month data at full granularity, but have a second, aggregated, table with all months in for hisory.

     

    Pete

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, adrianal 

     

    You may try using query parameter to filter data, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column as below.

    Date.Year([Date])*100+Date.Month([Date])

     

     

    Then you may go to 'Home'=>'Manage Parameters', create a new parameter as below.

     

    Then you need to use the parameter to filter the result.

     

    Finally you may modify the parameter in power bi desktop to get the corresponding result.

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.