How to Create dasbhaord in PowerBI, that shows the list of Test Cases which do not have Attachment?

[sravs2007]

Can anyone tell me how to get llist of Test Cases which do not have Attachment(Tested Evidence) in ADO Test Plan ..and create dasbhoard in PowerBI.

[v-venuppu]

Hi @sravs2007 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @sravs2007 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rohit1991]

Hi @sravs2007 

To build a Power BI dashboard that lists all test cases without attachments (test evidence) from Azure DevOps Test Plans, you’ll need to use the Azure DevOps REST APIs. Unfortunately, Analytics Views and OData feeds don’t expose attachments, so the REST API is the way to go here.

First, connect to the Azure DevOps REST API to pull both your test cases and all attachments. You can do this with Power Query in Power BI, usually by setting up two queries, one for test cases, and one for attachments, using your organization and project details. If you hit any 404 errors, double-check that your organization and project names are correct and that your Personal Access Token (PAT) has permission for test management and work item read.

Once you have both datasets in Power BI, merge or join them on Test Case ID (or whatever unique field connects them). The goal is to align each test case with its related attachments. For test cases that don’t have any attachment, the related AttachmentID or similar field will be blank.

Now, you can filter your report or build a DAX measure to only show test cases where the attachment field is blank. This gives you a clean list of all test cases missing evidence. Here’s a simple DAX pattern for the measure, but you can also just use visual-level filters:

Test Cases Without Attachments =
CALCULATE(COUNTROWS('TestCases'), ISBLANK('Attachments'[AttachmentID]))

Finish by building your dashboard visuals, tables, cards, or lists, to show and track the test cases without attachments. This makes it easy for QA teams to see which items need evidence added.

 if you have a lot of test cases, calling the REST API per test case can get slow, so try to batch requests or work with your DevOps admin to get a larger export if needed. Also, always make sure your PAT has the right permissions before you start, or you might spend time troubleshooting access errors.

This approach is the most direct and reliable way as of now, since there isn’t a shortcut using Analytics View or OData feeds alone. If you need a sample Power Query (M) function for calling the API, let me know and I can share a template.

[sravs2007]

Am not finding an option to add in Analytics View and OData feed. as well

[v-venuppu · Accepted solution]

Hi @sravs2007 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna @rohit1991  for the prompt response.

Since Attachments aren't exposed via Analytics Views/OData, use the Azure DevOps REST API to retrieve both:

• Test Cases
• Test Case Attachments

Here's few instructions:

1.Connect Power BI to Azure Devops:

Use Azure DevOps Analytics Views or the REST API to fetch data on test cases and their attachments. Since the "Attachment Count" field isn't available, you can use the Test Cases API and Attachments API in the Azure DevOps REST API documentation.

2.Fetch Data:

Retrieve test cases and check if attachments exist using the Test Cases API and Attachments API. You can filter out test cases with no attachments by identifying where Attachment Count = 0.

3.Create Relationships:

Create a relationship between the test cases and their corresponding attachments based on Test Case ID or a similar key.

4.Create Measures:

Create a measure to identify test cases without attachments:

Test Cases Without Attachments =
CALCULATE(
    COUNTROWS(TestCases),
    FILTER(TestCases, ISBLANK([AttachmentID]))
)

5.Design Table Visualization:

Create a table visualization in Power BI to display test case details, filtered to show only those without attachments.

Test Cases API:

Test Suites - Get Test Cases - REST API (Azure DevOps Test) | Microsoft Learn

Attachments API:

Test Suites - REST API (Azure DevOps Test) | Microsoft Learn

Loop over the list of Test Case IDs and retrieve attachments.

You can script this using Power Query (M) in Power BI with a custom function to call this API per Test Case ID.

• Ensure you have the necessary permissions to access Azure Devops data.
• Use Power Query to transform and filter the data as needed.
• Publish the report to Power BI Service to pin it to a dashboard for a live view of all test cases missing evidence.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

 

[sravs2007]

Am Getting 404 error when i try to hit these 2 APIs (Updated Project and Org in APIs) 

[v-venuppu]

Hi @sravs2007 ,

Can you try copy and paste links in browser as I was able to open it.

Thank you.

[v-venuppu]

Hi @sravs2007 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Akash_Varuna]

Hi @sravs2007 You could try by connecting Power BI to Azure DevOps through Analytics Views or the REST API to fetch data on test cases and their attachments. Then, use Power Query to filter out test cases with no attachments by identifying where Attachment Count = 0 or linking test case data with attachment details. Finally, create a table visualization in Power BI to display test case details, filtered to show only those without attachments.

[sravs2007]

@Akash_Varuna i cannot find the attached count field to add in Analytics View .. regarding RestAPI.. could you please give me sample restAPi if you know.. it would be greatful

[Akash_Varuna]

@sravs2007 Since the "Attachment Count" field isn't available, you could check the Test Cases API and Attachments API in the Azure DevOps REST API documentation. Use these to retrieve test cases and check if attachments exist. Here are the relevant docs:

1. REST API Overview

2. Test Cases API

3. Attachments API