Forum Discussion

Lorentzen's avatar
Lorentzen
Frequent Visitor
5 years ago

Importing DevOps data into Powerbi and filtering by team

Hi guys, The load script (m script) in Powerbi is not my strong side. I have been able to load in data correctly by area with the use of the following guide: https://docs.microsoft.com/en-us/a...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Lorentzen ,

 

As the document said:

The Team filter requires that the "AreaSK" field is included in the query used by the report you want to filter. 

 

$filter=startswith(AreaPath, '{areapath}') returns all teams mapped to an Area Path at or under the specified {areapath}. To include all teams in a project, omit this statement.

 

 

In addition, I found a discussion said: they could not find any way to group the results based on teams. You can get all the teams using the Teams Rest API. Or did you omit the teams filters entirely to have a try?

 

 

Refer to

Add a Team slicer to a Power BI report - Azure DevOps | Microsoft Docs

 

Use OData queries to generate Power BI reports - Azure DevOps | Microsoft Docs

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lorentzen's avatar
Lorentzen
Frequent Visitor
5 years ago

Hi Eyelyn.

Thank you for replying. Unfortunately it is still not clear for me how to do this.

When i simply copy paste this, and change to the correct organization and project:

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot? $apply=filter( WorkItemType eq 'User Story' and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) and StateCategory ne 'Completed' and DateValue ge Iteration/StartDate and DateValue le Iteration/EndDate and Iteration/StartDate le now() and Iteration/EndDate ge now() ) /groupby( (DateValue,State,WorkItemType,Priority,Area/AreaPath,Iteration/IterationPath), aggregate($count as Count, StoryPoints with sum as TotalStoryPoints) )

I get the following error:

DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (VS403483: The query specified in the URI is not valid: ')' or ',' expected at position 111 in 'filter( WorkItemType eq 'User Story' and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) and StateCategory ne 'Completed' and DateValue ge Iteration/StartDate and DateValue le Iteration/EndDate and Iteration/StartDate le now() and Iteration/EndDate ge now() ) /groupby( (DateValue,State,WorkItemType,Priority,Area/AreaPath,Iteration/IterationPath), aggregate($count as Count, StoryPoints with sum as TotalStoryPoints) )'..)
Details:
DataSourceKind=OData
DataSourcePath=https://analytics.dev.azure.com/HIDDEN/HIDDEN/_odata/v3.0-preview/WorkItemSnapshot
Url=https://analytics.dev.azure.com/HIDDEN/HIDDEN/_odata/v3.0-preview/WorkItemSnapshot? $apply=filter( WorkItemType eq 'User Story' and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) and StateCategory ne 'Completed' and DateValue ge Iteration/StartDate and DateValue le Iteration/EndDate and Iteration/StartDate le now() and Iteration/EndDate ge now() ) /groupby( (DateValue,State,WorkItemType,Priority,Area/AreaPath,Iteration/IterationPath), aggregate($count as Count, StoryPoints with sum as TotalStoryPoints) )