Calculated table based on Direct Query won't refresh

[JoannaSK]

My dashboard has a Direct Query connection to a cube data source that I don't own (which means I can't make any changes to the source data).

I created a dynamic calendar table:

Calendar = CALENDAR(MIN(Fact_PurchaseOrder[DIM_POCreatedDateValue]), MAX(Fact_PurchaseOrder[DIM_PODeliveryDateValue]))

However, when I publish the dashboard to My Workspace, I get this message:

We cannot refresh this dataset because the dataset contains calculated tables or calculated columns based on data from a Single Sign-on (SSO)-enabled Direct Query data source. Please configure the dataset to use an explicit connection with granular access control to access this data source and then try again.

How do I create a calendar table which will adjust to include all relevant dates, and which will refresh successfully when published?

[Shai_Karmani · Accepted solution]

Yepp, that approach works fine for refresh  TODAY(), EDATE(), EOMONTH() etc. don't touch the DirectQuery source, so they're evaluated entirely at refresh time and the SSO restriction doesn't apply.

One thing though: ENDOFQUARTER expects a date column, not a scalar, so ENDOFQUARTER(TODAY()) won't compile. Compute the current quarter-end as a scalar yourself, maybe something like this:

Calendar =VAR CurrentQEnd  = EOMONTH(TODAY(), 2 - MOD(MONTH(TODAY()) - 1, 3))VAR EndDate      = EDATE(CurrentQEnd, 18)RETURNADDCOLUMNS(    CALENDAR(DATE(2015,1,1), EndDate),    "Year",     YEAR([Date]),    "Month",    FORMAT([Date],"MMM"),    "MonthNum", MONTH([Date]))

CurrentQEnd lands on the last day of the current quarter; EDATE(..., 18) pushes it 18 months forward, giving you 6 full quarters out. The table re-evaluates on every refresh, so the upper bound extends automaticaly over time.

If quarter-end alignment isn't important to you, EOMONTH(TODAY(), 18) is a simpler one-liner that just gives end of month 18 months from today....

[Shai_Karmani]

That error is a known service-side limitation: calculated tables and calculated columns that reference an SSO-enabled DirectQuery source cannot be refreshed in the service. The cleanest fix is to make the date table independent of the DirectQuery source so refreshing it does not need any SSO data to evaluate.

A simple approach is a static range calendar wide enough to cover anything you will realistically need, for example:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2015,1,1), DATE(2035,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date],"MMM"),
    "MonthNum", MONTH([Date])
)

You can also build the calendar in Power Query using List.Dates, which has the same effect without referencing the cube. If you only want to show "active" PO dates in your visuals, handle that with a slicer or a measure rather than by trying to limit the calendar definition itself.

If this helped, please give a thumbs up and mark it as resolved.

Thank you,
Shai Karmani

[JoannaSK]

How do I make a calendar table that will expand automatically to include new dates as time passes?

If I want the table to always extend 6 full quarters into the future, can I just put EDATE(ENDOFQUARTER(TODAY()),18) in place of the second DATE() in the calendar expression?