Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |