Forum Discussion

Samand's avatar
Samand
Frequent Visitor
1 year ago
Solved

Summary table utilizing dynamic dates from slicer

I've got 4 tables: 1. dim_Dates - Dax table with dates from the minimum date in fact_AcqDisp to today(). 2. fact_AcqDisp - from SharePoint and pre-prepared through Power Query. One row per building...
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Samand's avatar
    Samand
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

Samand's avatar
Samand
Frequent Visitor
1 year ago

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.