Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Isha
Frequent Visitor

Consolidate data for creating visualization

Hi,

 

I am using an excel based testing metrics to track the day of testing (Sprint _execution_ Date) and status for the test cases executed on that day(Sprint_status). These sprints repeat as and when test cases are executed and corresponding status are populated by testers. Also, we have multiple applications.

 

I want to use Power BI to create the visualizations to interpret :

 

1. Days testcases were executed for all applications, how many were executed, how many passed and how many failed.

2. Using a slicer, if I select individual application , date of execution, status of execution and total executed are displayed.

3. If all applications are selected, same data is shown as consolidated for all apps.

 

The number of sprints will increase as and when testing is conducted.

 

Below is the snapshot of the data:

 

ApplicationTest Case #_Sprint_33_Execution_DateSprint 33 Status_Sprint_34_Execution_DateSprint 34 Status_Sprint_35_Execution_DateSprint 35 Status
Application 1P1356/21/2016Fail6/22/2016Fail6/23/2016Fail
Application 1P1366/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 1P1376/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 1P1386/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 1P1396/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 1P1406/21/2016Fail6/22/2016Fail6/23/2016Fail
Application 1P1416/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 1P142      
Application 1P143      
Application 2C16/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C26/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C36/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C46/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C56/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C66/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C76/21/2016Fail6/22/2016Fail6/23/2016Pass
Application 2C86/21/2016Fail6/22/2016Fail6/23/2016Pass
Application 2C96/21/2016Fail6/22/2016Pass6/23/2016Pass
Application 2C106/21/2016Fail6/22/2016Pass6/23/2016Pass
Application 2C116/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C126/21/2016Fail6/22/2016Pass6/23/2016Pass
Application 2C136/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C146/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C156/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C166/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C176/21/2016Pass6/22/2016Pass6/23/2016Pass
Application 2C186/21/2016Pass6/22/2016Pass6/23/2016Pass
1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@Isha

 

I think the very first thing in your case is to re-model the dataset.

Table 2 = 
UNION (
    ADDCOLUMNS(ALL (
        'Table'[Application],
        'Table'[Test Case #],
        'Table'[_Sprint_33_Execution_Date],
        'Table'[Sprint 33 Status]
    ),"Sprint_Excute","33 Exection"),
    ADDCOLUMNS(ALL (
        'Table'[Application],
        'Table'[Test Case #],
        'Table'[_Sprint_34_Execution_Date],
        'Table'[Sprint 34 Status]
    ),"Sprint_Excute","34 Exection"),
    ADDCOLUMNS(ALL (
        'Table'[Application],
        'Table'[Test Case #],
        'Table'[_Sprint_35_Execution_Date],
        'Table'[Sprint 35 Status]
    ),"Sprint_Excute","35 Exection")
)

Capture.PNG

 

Then your questions would be much easier.

View solution in original post

2 REPLIES 2
Eric_Zhang
Employee
Employee

@Isha

 

I think the very first thing in your case is to re-model the dataset.

Table 2 = 
UNION (
    ADDCOLUMNS(ALL (
        'Table'[Application],
        'Table'[Test Case #],
        'Table'[_Sprint_33_Execution_Date],
        'Table'[Sprint 33 Status]
    ),"Sprint_Excute","33 Exection"),
    ADDCOLUMNS(ALL (
        'Table'[Application],
        'Table'[Test Case #],
        'Table'[_Sprint_34_Execution_Date],
        'Table'[Sprint 34 Status]
    ),"Sprint_Excute","34 Exection"),
    ADDCOLUMNS(ALL (
        'Table'[Application],
        'Table'[Test Case #],
        'Table'[_Sprint_35_Execution_Date],
        'Table'[Sprint 35 Status]
    ),"Sprint_Excute","35 Exection")
)

Capture.PNG

 

Then your questions would be much easier.

Thanks Eric. I will try this solution and let you know. Like you suggested,  making the table single-dimensional will help me in this scenario.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.