Hi,
Yep this is a royale pain in the neck. The Push DataSet does not appear to be able to apply a "IsDateTable" attribute to a dataset.
I also tried to use Measures to create the colums, using CALENDARAUTO() howver, this does not appear to work either.
As a hack solution for this, I generate a table using the API, then simply generate the columns in code: ex: In fact, even more is broken, as you cannot "sort" a visual by a non-visual column, as is often required for Month, or other Alpha fields, as the default sort is Alph ion the visual. Sure would like for this to work a bit better.
Since you cant designate a table as a "Special Date Table", you have to explicitly add a relationship to the Date and cols.
Simple hack code:
var dataRowsReq = new PostRowsRequest();
// PSH - Because I cant get CALENDARAUTO() DAX to work, gonna generate them here...
int monthIdx = 0;
int quarterIdx = 0;
var calPrev = new CalendarPbi();
var dateStart = new DateTime(2015, 1, 1);
var dateEnd = new DateTime(2021, 12, 31);
var dates = new List<CalendarPbi>();
for (var dt = dateStart; dt <= dateEnd; dt = dt.AddDays(1))
{
var cal = new CalendarPbi();
cal.Date = dt.ToString("MM/dd/yyyy");
cal.Year = String.Format ("CY {0:yyyy}", dt);
cal.Month = String.Format("{0:yyyy MM}", dt); // PBI BrainDead - Need to sort here...
cal.Quarter = String.Format("{0:yyyy} Q{1}", dt, ((dt.Month + 2) / 3));
// Sort Idxss...
if (cal.Month != calPrev.Month)
{
monthIdx++;
}
if (cal.Quarter != calPrev.Quarter)
{
quarterIdx++;
}
cal.MonthIdx = monthIdx;
cal.QuarterIdx = quarterIdx;
dates.Add(cal);
calPrev = cal;
}
// Paginate load into PowerBI...
var inc = 10000;
var skip = 0;
var cnt = 0;
while (cnt < dates.Count())
{
dataRowsReq.Rows = dates.Skip(skip).Take(inc).Cast<Object>().ToList();
client.Datasets.PostRowsInGroup(workspaceId, datasetId, TABLE_NAME_CALENDAR, dataRowsReq);
cnt += inc;
skip += inc;
}
