KPI dashboards in SCADA systems - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Downtime for a Specific Machine

You have a table MachineEvents with columns MachineID, EventType, and DurationMinutes. You want to calculate the total downtime for MachineID 101 using a DAX measure.

Which DAX expression correctly calculates this total downtime?

A TotalDowntime = CALCULATE(SUM(MachineEvents[DurationMinutes]), FILTER(MachineEvents, MachineEvents[MachineID] = 101 && MachineEvents[EventType] = "Downtime"))

B TotalDowntime = CALCULATE(SUM(MachineEvents[DurationMinutes]), MachineEvents[MachineID] = 101 && MachineEvents[EventType] = "Downtime")

C TotalDowntime = SUMX(FILTER(MachineEvents, MachineEvents[MachineID] = 101 && MachineEvents[EventType] = "Downtime"), MachineEvents[DurationMinutes])

D TotalDowntime = SUM(MachineEvents[DurationMinutes]) WHERE MachineEvents[MachineID] = 101 AND MachineEvents[EventType] = "Downtime"

💡 Hint

Use CALCULATE with a FILTER to apply multiple conditions.

Option A correctly uses CALCULATE with a FILTER to sum DurationMinutes only for rows where MachineID is 101 and EventType is "Downtime". Option A is invalid because filter conditions inside CALCULATE require FILTER or column filters, not direct logical expressions. Option A works but is less efficient. Option A is invalid syntax.

❓ visualization

intermediate

Best Visualization for Machine Efficiency Over Time

You want to show the efficiency of a machine over the past 12 months on a KPI dashboard. Efficiency is a percentage value updated monthly.

Which visualization type is best to clearly show trends and monthly changes?

A A pie chart showing efficiency percentages for each month

B A line chart with months on the x-axis and efficiency percentage on the y-axis

C A stacked bar chart showing efficiency and downtime side by side

D A scatter plot with efficiency on the x-axis and months on the y-axis

💡 Hint

Think about how to best show changes over time.

A line chart is ideal for showing trends over time because it connects data points in order, making it easy to see increases or decreases. Pie charts are not good for time series. Stacked bar charts can be confusing for percentages over time. Scatter plots do not show time progression clearly.

❓ data_modeling

advanced

Designing a Data Model for Multi-Machine KPI Dashboard

You need to build a KPI dashboard that shows downtime, production count, and efficiency for multiple machines. Data comes from separate tables: Machines, Events, and Production. Which data modeling approach is best to enable easy KPI calculations and filtering by machine?

A Merge all tables into one large table with all columns combined

B Keep tables separate without relationships and use LOOKUPVALUE in measures

C Create relationships between <code>Machines[MachineID]</code> and <code>Events[MachineID]</code>, and between <code>Machines[MachineID]</code> and <code>Production[MachineID]</code>, then use <code>Machines</code> as a filter table

D Create a single table with only machine names and aggregate all data in measures without relationships

💡 Hint

Think about how relationships help filtering and calculations.

Option C is best practice: using a Machines table as a dimension connected to fact tables Events and Production allows easy filtering and aggregation. Merging all data (B) can cause redundancy and performance issues. Using LOOKUPVALUE (C) is less efficient and complex. Option C loses detail and filtering ability.

🔧 Debug

advanced

Identify the Error in a DAX Measure for Average Production

Given this DAX measure to calculate average production per day:

AvgProduction = AVERAGE(Production[Count])

It returns incorrect results when filtered by machine and date. What is the likely cause?

A The measure does not consider the date context, so averages over all data ignoring filters

B The <code>AVERAGE</code> function cannot be used on numeric columns

C The <code>Production[Count]</code> column contains text values causing errors

D The measure should use <code>SUM</code> instead of <code>AVERAGE</code> to get correct results

💡 Hint

Think about how context affects aggregation in DAX.

Option A is correct because AVERAGE respects filter context, but if the data model or filters are not set properly, the measure might ignore date or machine filters. The issue is usually missing relationships or incorrect filter context, not the function itself. Option A is false; AVERAGE works on numeric columns. Option A changes the aggregation type incorrectly. Option A is unlikely if data is numeric.

🧠 Conceptual

expert

Optimizing KPI Dashboard Performance with Large Data

Your KPI dashboard slows down significantly when loading data from millions of SCADA system events. Which strategy will best improve performance without losing important KPI accuracy?

A Remove all filters and show only total KPIs to reduce calculation time

B Load all raw event data into the dashboard and apply filters dynamically at runtime

C Use complex calculated columns on raw data tables to compute KPIs on the fly

D Implement data aggregation tables that pre-calculate daily summaries for KPIs and use them in the dashboard

💡 Hint

Think about pre-processing data to reduce load.

Option D is best because pre-aggregating data reduces the volume the dashboard processes, improving speed while keeping KPI accuracy. Option D causes slowdowns due to large data volume. Option D loses detail and usefulness. Option D increases calculation complexity and slows performance.

Practice

1. What is the main purpose of a KPI dashboard in a SCADA system?

easy

A. To generate detailed reports for auditors

B. To store raw sensor data for later analysis

C. To control machine operations automatically

D. To display key performance indicators clearly and quickly

Solution

1. Step 1: Understand the role of KPI dashboards

   KPI dashboards focus on showing important numbers that reflect system performance.

2. Step 2: Differentiate from other SCADA functions

   Storing data, controlling machines, and generating reports are different tasks from displaying KPIs.

3. Final Answer:

   To display key performance indicators clearly and quickly -> Option D

4. Quick Check:

   KPI dashboard = clear key numbers [OK]

Hint: KPI dashboards show key numbers fast and clear [OK]

Common Mistakes:

• Confusing dashboards with data storage
• Thinking dashboards control machines
• Mixing dashboards with report generation

2. Which of the following is the correct way to highlight a KPI value that is below target using color in a dashboard?

easy

A. Use red color for values below target

B. Use yellow color for values below target

C. Use blue color for values below target

D. Use green color for values below target

Solution

1. Step 1: Understand color conventions in KPI dashboards

   Red usually signals a problem or below target performance.

2. Step 2: Match color to KPI status

   Green means good, red means bad, so below target should be red.

3. Final Answer:

   Use red color for values below target -> Option A

4. Quick Check:

   Below target = red color [OK]

Hint: Red means below target or problem [OK]

Common Mistakes:

• Using green for bad performance
• Choosing blue which is neutral
• Using yellow which means caution, not failure

3. Given a SCADA KPI dashboard showing machine uptime percentages: Machine A = 95%, Machine B = 88%, Machine C = 92%. If the target uptime is 90%, which machines should be highlighted in red?

medium

A. Machine B only

B. Machine A and Machine C

C. Machine A only

D. Machine B and Machine C

Solution

1. Step 1: Compare each machine's uptime to the target

   Machine A: 95% > 90% (above target), Machine B: 88% < 90% (below target), Machine C: 92% > 90% (above target).

2. Step 2: Identify which machines are below target

   Only Machine B is below the 90% target and should be highlighted in red.

3. Final Answer:

   Machine B only -> Option A

4. Quick Check:

   Below 90% uptime = red highlight [OK]

Hint: Highlight only below target values in red [OK]

Common Mistakes:

• Highlighting machines above target
• Including machines that meet or exceed target
• Ignoring the target threshold

4. You created a KPI dashboard but the color coding does not change when values go below target. What is the most likely cause?

medium

A. The data source is missing some sensor readings

B. The dashboard layout is too complex

C. The color rules are not linked to the KPI values correctly

D. The target values are set too high

Solution

1. Step 1: Check the color coding logic

   If colors do not change, the rules linking KPI values to colors are likely not set or connected properly.

2. Step 2: Rule out other causes

   Layout complexity or missing data won't directly stop color changes; target values affect performance but not color logic.

3. Final Answer:

   The color rules are not linked to the KPI values correctly -> Option C

4. Quick Check:

   Color rules linked properly = color changes [OK]

Hint: Check color rule connections first when colors don't update [OK]

Common Mistakes:

• Blaming layout instead of logic
• Assuming missing data causes color issues
• Thinking target values affect color coding directly

5. You want to design a KPI dashboard for a SCADA system that shows temperature, pressure, and flow rate KPIs. Which layout approach best supports quick understanding?

hard

A. Display all raw sensor data in a large table with no colors

B. Arrange KPIs in a simple grid with clear labels and color-coded status

C. Show only the average values without any target or color indicators

D. Use multiple pages with detailed charts for each KPI

Solution

1. Step 1: Identify best practices for KPI dashboard layout

   Simple grids with clear labels and color coding help users quickly see status and compare KPIs.

2. Step 2: Evaluate other options

   Raw data tables, averages without targets, or multi-page charts reduce clarity and speed of understanding.

3. Final Answer:

   Arrange KPIs in a simple grid with clear labels and color-coded status -> Option B

4. Quick Check:

   Simple, clear, color-coded layout = fast understanding [OK]

Hint: Use simple grids and colors for fast KPI reading [OK]

Common Mistakes:

• Showing raw data instead of KPIs
• Omitting targets and colors
• Splitting KPIs across many pages