Forum Discussion
Summary table utilizing dynamic dates from slicer
Hi Anonymous
I've solved this by creating a DAX table using GENERATESERIES() to generate 10 rows representing the last 10 years. I cross-joined this table with one of my existing tables and added calculated columns derived from other tables. This gives me a row of data for each building for each year, allowing me to filter by year. Since there are only a couple of hundred buildings, it works well, but I might need to limit the number of years for performance reasons.
Thank you Sahir_Maharaj
Hi, Samand
In import mode, after the data is imported into a Power BI model, you can't dynamically generate a calculated table based on the slicer. You'll need to change the connection mode to direct query and then do this with the parameters in Power Query.
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jianpeng Li,
Thanks for your response. I don't think that you can change a SharePoint list to Direct Query and it's the only source I can use.
Hi, Samand
Thank you very much for your reply!
Thank you for your question. The slicer you mentioned really doesn't affect the calculated table, it only works when the slicer is working. At the same time, it's true that the SharePoint list you're using can't be converted to Direct Query mode.
Currently, slicer-based dynamic table generation is only possible in Power BI with Direct Query mate parameters. This is a current limitation and we understand that this may cause some inconvenience.
If you have additional needs or need further assistance, please feel free to let us know. We will do our best to provide you with support and solutions.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Thanks again. So there is no available solution to what I would like to achieve.
Hi, Samand
Thank you for your reply. If you want to do this, you can still do things like read your SharePoint into a database, and then Power BI connects to those databases to do what you want. Or you just need to create some measures that use summarize to create some virtual tables, and then reflect the corresponding results in the visuals.
Best Regards
Jianpeng Li