Consolidate data for creating visualization

Question

Hi,&nbsp;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.&nbsp;I want to use Power BI to create the visualizations to interpret :&nbsp;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.&nbsp;The number of sprints will increase as and when testing is conducted.&nbsp;Below is the snapshot of the data:&nbsp;ApplicationTest Case #_Sprint_33_Execution_DateSprint 33 Status_Sprint_34_Execution_DateSprint 34 Status_Sprint_35_Execution_DateSprint 35 StatusApplication 1P1356/21/2016Fail6/22/2016Fail6/23/2016FailApplication 1P1366/21/2016Pass6/22/2016Pass6/23/2016PassApplication 1P1376/21/2016Pass6/22/2016Pass6/23/2016PassApplication 1P1386/21/2016Pass6/22/2016Pass6/23/2016PassApplication 1P1396/21/2016Pass6/22/2016Pass6/23/2016PassApplication 1P1406/21/2016Fail6/22/2016Fail6/23/2016FailApplication 1P1416/21/2016Pass6/22/2016Pass6/23/2016PassApplication 1P142&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Application 1P143&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Application 2C16/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C26/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C36/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C46/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C56/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C66/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C76/21/2016Fail6/22/2016Fail6/23/2016PassApplication 2C86/21/2016Fail6/22/2016Fail6/23/2016PassApplication 2C96/21/2016Fail6/22/2016Pass6/23/2016PassApplication 2C106/21/2016Fail6/22/2016Pass6/23/2016PassApplication 2C116/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C126/21/2016Fail6/22/2016Pass6/23/2016PassApplication 2C136/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C146/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C156/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C166/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C176/21/2016Pass6/22/2016Pass6/23/2016PassApplication 2C186/21/2016Pass6/22/2016Pass6/23/2016Pass

eric_zhang · Accepted Answer

Isha
&nbsp;
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")
)

&nbsp;
Then your questions would be much easier.

Forum Discussion

Consolidate data for creating visualization

2 Replies

Related content

Power BI - Deneb visual - Read a JSON to create the visuals

Consolidating several income sheets

Consolidation

Wanna Create Waterfall Visual

Creating ICMs via Activator

Application	Test Case #	_Sprint_33_Execution_Date	Sprint 33 Status	_Sprint_34_Execution_Date	Sprint 34 Status	_Sprint_35_Execution_Date	Sprint 35 Status
Application 1	P135	6/21/2016	Fail	6/22/2016	Fail	6/23/2016	Fail
Application 1	P136	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 1	P137	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 1	P138	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 1	P139	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 1	P140	6/21/2016	Fail	6/22/2016	Fail	6/23/2016	Fail
Application 1	P141	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 1	P142
Application 1	P143
Application 2	C1	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C2	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C3	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C4	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C5	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C6	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C7	6/21/2016	Fail	6/22/2016	Fail	6/23/2016	Pass
Application 2	C8	6/21/2016	Fail	6/22/2016	Fail	6/23/2016	Pass
Application 2	C9	6/21/2016	Fail	6/22/2016	Pass	6/23/2016	Pass
Application 2	C10	6/21/2016	Fail	6/22/2016	Pass	6/23/2016	Pass
Application 2	C11	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C12	6/21/2016	Fail	6/22/2016	Pass	6/23/2016	Pass
Application 2	C13	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C14	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C15	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C16	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C17	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass
Application 2	C18	6/21/2016	Pass	6/22/2016	Pass	6/23/2016	Pass