Need 2 line charts specifying DetectedDefectCountVSdetected_WeekNo, ClosedDefectCountVSclosed_weekno

[Anonymous]

Hi All,

I am a newbie to PowerBI and working on a small project case study.

I have a data set comprising of

1. Defect_ID

2. Status

3. Detected_WeekNo

4. Closed_WeekNo (populated if the status of defect is set to close).

I need to create a visual which has

1. Detected_WeekNo on X-Axis

2. Defect_ID count on the Y axis

3. One line chart on the visual, showing the count of defects per each value of Detected_WeekNo.

4. One line chart on the same visual, showing the count of defects per each value of Closed_WeekNo.

Sample Data set 'DefectDump':-

Defect_ID	Status	Detected_WeekNo	Closed_WeekNo
1	New	20
2	In Progress	20
3	Closed	20	20
4	Retest	20
5	Closed	20	21
6	Closed	21	21

My queries are:-

1. Which visual in PowerBI desktop will be best suitable for representing this information?

2. Based on the above described sample data set, the chart should specify the Defected_defect_count for week 20 as 4, Detected_defect_count for week 21 as 1, Closed_defect_count for week 20 as 1, Detected_defect_count for week 21 as 2. How to achieve this? Does it need a self join between the 'DefectDump' table data set?

How to implement this requirement?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could combine your "Detected_WeekNo" and "Closed_WeekNo" into one column as your x-axis.

DC =
IF (
    'Table'[Detected_WeekNo] >= 'Table'[Closed_WeekNo],
    'Table'[Detected_WeekNo],
    'Table'[Closed_WeekNo]
)

Then create two measures to caculate values of "Defect_ID".

per each value of Closed_WeekNo =
CALCULATE (
    COUNT ( 'Table'[Defect_ID] ),
    ALLEXCEPT ( 'Table', 'Table'[Closed_WeekNo] ),
    FILTER ( 'Table', 'Table'[Closed_WeekNo] = 'Table'[DC] )
)

per each value of Detected_WeekNo =
CALCULATE (
    COUNT ( 'Table'[Defect_ID] ),
    ALLEXCEPT ( 'Table', 'Table'[Detected_WeekNo] ),
    FILTER ( 'Table', 'Table'[Detected_WeekNo] = 'Table'[DC] )
)

 Use line chart to show the results.

Here is my test file for your reference.

[Anonymous]

thanks a lot, @v-eachen-msft . 

The solution worked brilliantly 🙂