Forum Discussion
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.
3. fact_Building - from SharePoint and preprepared in PowerQuery. Contains building details. One row per building.
4. fact_Measurements - from SharePoint and preprepared in PowerQuery. Contains measurements. Multiple rows per building.
Tables 2 - 4 are linked through a matching Ref_ID
I've put the dates from table 1 into a "between" slicer.
Tables 2 & 4 both have FROM and TO date fields.
I need to create a summary table, based on grouping from a column (Building_Type) in table 3, which calculates the number of linked rows in Table 2 & 3, where tables 2 & 3 are filtered by dates from the slicer. It also needs to sum the measurements from Table 4 based on the slicer dates.
Does anybody have any suggestions on how to do this?
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.
9 Replies
Hello Samand,
Can you please try this consolidated DAX for a calculated table:
SummaryTable = SUMMARIZE( 'fact_Building', 'fact_Building'[Building_Type], "Total Rows AcqDisp", CALCULATE( COUNTROWS('fact_AcqDisp'), 'fact_AcqDisp'[FROM] <= MAX('dim_Dates'[Date]) && 'fact_AcqDisp'[TO] >= MIN('dim_Dates'[Date]) ), "Total Rows Building", CALCULATE( COUNTROWS('fact_Building'), 'fact_AcqDisp'[FROM] <= MAX('dim_Dates'[Date]) && 'fact_AcqDisp'[TO] >= MIN('dim_Dates'[Date]) ), "Total Measurements", CALCULATE( SUM('fact_Measurements'[Measurement_Value]), 'fact_Measurements'[FROM] <= MAX('dim_Dates'[Date]) && 'fact_Measurements'[TO] >= MIN('dim_Dates'[Date]) ) )
Thanks for your response.This doesn't work. I Think it's because DAX tables don't recalculate based on slicers. When referencing dim_Dates it uses all of the dates. I think only measures recalculate based on slicer selections.
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.
