Forum Discussion

opyke's avatar
opyke
Regular Visitor
3 years ago

Filter before calculated table generation

Hi,

 

I am using the following DAX to create a calculated table which summarises/totals data for University applications and fills in week end dates where there is no data.

 

 

 

ApplicationSummary =

    VAR MissingDates = GENERATESERIES (
                            MIN('Application'[SubmissionWeekEndDate]),
                            MAX('Application'[SubmissionWeekEndDate]),
                            7 )
                               
    VAR MissingDatesWithAddedColumns = ADDCOLUMNS(
                                            MissingDates,
                                            "Programme_Level__c", BLANK(),
                                            "Requested_Academic_Year__c", BLANK(),
                                            "Requested_Intake__c", BLANK(),
                                            "Total Applications", BLANK(),
                                            "Total Application Offers", BLANK(),
                                            "Total Application Firm Acceptances", BLANK()
                                        )                                           
   
    VAR SummarizedTotals = SUMMARIZECOLUMNS(
                                'Application'[SubmissionWeekEndDate],
                                'Application'[Programme_Level__c],
                                'Application'[Requested_Academic_Year__c],
                                'Application'[Requested_Intake__c],
                                "Total Applications",
                                COUNTROWS('Application'),
                                "Total Application Offers",
                               SUMX('Application',IF('Application'[hed__Application_Status__c] IN {"Conditional Offer", "Unconditional Offer"},1,0)),
                                "Total Application Firm Acceptances",
                                SUMX('Application',IF('Application'[Offer_Reply__c] = "Firm",1,0))
                            )

    RETURN GENERATEALL(MissingDates, FILTER(SummarizedTotals,'Application'[SubmissionWeekEndDate] = [Value]))

 

 

 

The data looks like this:

 

 

This calculated table is used for two visuals and there are slicers which are used to filter on Requested Intake, Requested Year and Programme data in the calculated table, and also to show/hide the Y-Axis values on each of the visuals.

 

 

The problem is that when filters are applied, the week end dates with no data are filtered out. i.e. In the new applications visual below, the first data point is 4th December 2020, the second is 8th Jan 2021. 11th, 18th, 25th and 1st Jan are missing, and this is because there is data on these dates, but with a different requested intake,

 

It seems that what I need to do is filter the source table before the data in the calculated table is generated.

 

Is this possible?

 

Thanks

1 Reply

  • Yes, but only directly in the definition of the calculated table. 

     

    Calculated tables and columns are computed only ONCE  during the dataset refresh.  They cannot be influenced by measures or filters   (one exception is when you create calculated tables temporarily inside a measure)