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.

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

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's avatar
    Samand
    Frequent Visitor
    1 year ago

    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.