Importing DevOps data into Powerbi and filtering by team

[Lorentzen]

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/azure/devops/report/powerbi/sample-boards-sprintburndown?view=azure...

Now i want to do the same, but instead of filtering by area I would like to be able to add Teams.

What i do not understand is, that with Area i have to define the project name. That makes sense. 
In the m-script below it seems like I have to define the teamname (marked with red)
I am actually interested in getting all the team names into my report and not define one.

I have ofcourse tried to run the below code multiple times without luck. (this code example is directly from the microsoft site - so i have put in the correct organization and project in my own script)

Can you help?

Code example:

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) )

[v-eqin-msft]

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]

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) )