Forum Discussion
Post PatronUsing Calculated Dates (Table) in SUMMARIZE in a measure
Hi,
I have a Dates Table which I calculated as follows
DatesTable =
ADDCOLUMNS (
CALENDAR (DATE(2019,4,1),TODAY()),
"DateAsInteger" ,FORMAT ( [Date], "YYYYMMDD" ),
"Year" ,YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
)
However, when I try to use this calculated table in SUMMARIZE function in a measure elswhere, the DatesTable does not appear in the Intellisense - as if it is not a valid Table.
Hi F_Reh
The issue arises because calculated tables, such as your DatesTable, exist at the model level and cannot be directly referenced within a measure using SUMMARIZE. Measures operate in a row context and are designed to return scalar values rather than tables, while SUMMARIZE is a table function that requires a valid table reference. Since calculated tables are not exposed in the same way as standard data model tables, they will not appear in Intellisense when writing a measure. To work around this, you can either use SUMMARIZE within a calculated table instead of a measure or create a variable within the measure that dynamically retrieves the required date context using functions like VALUES(), SELECTCOLUMNS(), or CROSSJOIN(). If your goal is to aggregate data based on the calculated DatesTable, consider using CALCULATE with ALL() or FILTER() to apply the necessary context while maintaining compatibility with Power BI's DAX engine. This ensures that the measure functions correctly within visuals while still leveraging the date logic from your calculated table.
1 Reply
rohit1991Super User
Hi F_Reh
The issue arises because calculated tables, such as your DatesTable, exist at the model level and cannot be directly referenced within a measure using SUMMARIZE. Measures operate in a row context and are designed to return scalar values rather than tables, while SUMMARIZE is a table function that requires a valid table reference. Since calculated tables are not exposed in the same way as standard data model tables, they will not appear in Intellisense when writing a measure. To work around this, you can either use SUMMARIZE within a calculated table instead of a measure or create a variable within the measure that dynamically retrieves the required date context using functions like VALUES(), SELECTCOLUMNS(), or CROSSJOIN(). If your goal is to aggregate data based on the calculated DatesTable, consider using CALCULATE with ALL() or FILTER() to apply the necessary context while maintaining compatibility with Power BI's DAX engine. This ensures that the measure functions correctly within visuals while still leveraging the date logic from your calculated table.