Summary table utilizing dynamic dates from slicer

[Samand]

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?

[Anonymous]

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]

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.

[Anonymous]

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.

[Samand]

Hi @Anonymous 

Thanks again. So there is no available solution to what I would like to achieve.

[Anonymous]

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

[Samand · Accepted solution]

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]

Hi, @Samand 

Ok. If you're experiencing performance issues, you can try the built-in performance monitoring tools in Power BI desktop:

Or you can use DAX studio to see the overall performance. If the performance penalty is not significant, then you can maintain your current practices. If the performance is slow, you need to generate the number of years as you say now.

I think if you only need this table, you can do it by entering the table without having to calculate the table generation, which will greatly improve the report performance.

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.

[Sahir_Maharaj]

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])
        )
)

[Samand]

Hi Sahir_Maharaj

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.