Data model in the Power BI Data Connector for VSTS


To connect to the Analytics Services for VSTS from the Power BI Desktop Data Connector, you must download the Power BI Desktop January 2018 Update or a newer version. You can download it from the official Power BI Desktop download page.

How historical data is modelled

Historical data is modelled as a periodic snapshot fact table. The fact table contains one row created at midnight for each work item at the end of each period. For example, history on a daily period is modeled as one row at midnight for each day, while a weekly period would be one row at midnight of the last day of the week.

The grain of this table is the period, not the individual work item. What this means is that a single Work Item will appear multiple times, once for each historical period. Selecting the last 30 days of history will result in a single work item appearing 30 times in the data model. If the work item has not changed within the last 30 day's the most recent revision of the work item is replicated on each day.

When working with the data connector we recommend using the Date column or filtering to the most recent instance of the period using the IsCurrent. For example, if you wanted to show a table of work items and values for the associated fields you would use Is Current as a filter which is set to True, that way you don't see duplicates of each of the work items included in the table. If you wanted instead to show a trend of work items based on state you would include the Date column on the Axis of the visualization.


Always use the Date option when using the Date column. Date is not intended to support default Hierarchies in Power BI.