How do I filter by team?

[nbrown02]

How do you filter by a team if you have multiple teams in the same Jira project? Is there any way to do this?

[nbrown02]

Assuming you have a custom field like the image shown, here are the steps involved...

1. Find out what the name of this custom field is. To do this enter the following in your web browser:
   https://[ADD YOUR JIRA URL HERE]/rest/api/2/search?jql=key%20%3D%20[ADD YOUR ISSUE ID HERE]

An example being: https://flowviz.atlassian.net/rest/api/2/search?jql=key%20%3D%20FV-29068

2. In the resulting page, do a CTRL + F for the team name, like so:
   

In this example, we can see the team name resides in customfield_11700 <- please note, this will very likely be different in your Jira project!

3. In your Power BI report, go to Power Query (click Transform Data in report view) and find the applied step where Data.Fields is expanded. Add in the field value to the first set of fields and give it a name of team. A new column called team should appear:

4. Click on the expander and choose 'Expand to new rows' (if prompted with an 'Insert Step' warning just click 'Insert')
   

5. Click the expander again and choose 'value' and hit ok
   

6. Now your team names will be populated (ignore the fact I have just one value of 'Alchemists' here, you should have multiple)
   

7. Repeat this step for WorkItems InProgress, WorkItems History, WorkItems History2, WorkItems History5, WorkItems WIP,WorkItems WIP 2 tables. Ensure every time the team column has the same name (e.g. team.value)

8. When done with all these tables hit 'Close & Apply'
   

9. When your data is refreshed, go to the table view:
   

10. Click on your WorkItems WIP 3 table and update the formula to be:
    WorkItems WIP 3 =
    UNION (
    SELECTCOLUMNS (
    'WorkItems WIP',
    "CompletedDate", [CompletedDate],
    "Date", [Date],
    "DateSK", [DateSK],
    "InProgressDate", [InProgressDate],
    "IssueID", [IssueID],
    "IssueType", [IssueType],
    "Project",[Project],
    "Today",[Today],
    "team.value",[Team.Value],
    "WIPAge",[WIPAge],
    "WIPAgeGrouped",[WIPAgeGrouped]
    ),
    SELECTCOLUMNS (
    'WorkItems WIP 2',
    "CompletedDate", [CompletedDate],
    "Date", [Date],
    "DateSK", [DateSK],
    "InProgressDate", [InProgressDate],
    "IssueID", [IssueID],
    "IssueType", [IssueType],
    "Project",[Project],
    "Today",[Today],
    "team.value",[Team.Value],
    "WIPAge",[WIPAge],
    "WIPAgeGrouped",[WIPAgeGrouped]
    )
    )

11. Click 'Table tools' then 'New table'
    

12. Enter the following formula:
    zzTeam =
    DISTINCT (
    UNION (
    SELECTCOLUMNS (
    'WorkItems Completed',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems History',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems History2',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems History3',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems History4',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems History5',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems InProgress',
    "Team", [team.value]
    ),
    SELECTCOLUMNS (
    'WorkItems WIP 3',
    "Team", [team.value]
    )
    )
    )

13. Next go to your report view and click Modelling > Manage relationships
    

14. Create a relationship between zzTeam and the work item tables of WorkItems Completed, WorkItems InProgress, WorkItems History, WorkItems History2, WorkItems History5, WorkItems WIP,WorkItems WIP 2 tables. It should be one to many or many to one

14. Add a slicer to your report pages from the zzTeam table and use this to do any filtering:
    

Alternatively, if you want to create a report for each team and filter at the query level, you would have to edit each query AND each startat function in Power BI...