Skip to main content

Lesson 14 · Power BI: from form to report

M
Written by Mairu

Path

Duration

Level

Before you start

🟠 Analysis

70 min

Advanced

[[08 · Después de guardar - histórico, móvil y PDF

After saving - history, mobile and PDF]] · [[13 · Dashboards - una pantalla para cada perfil

Dashboards - a screen for each profile]] · Basic notions of Power BI

The dashboard answers what is happening now?. Power BI answers what is happening over time, and why?. In this lesson you will take the packaging controls into a report and see why the design decisions of the previous lessons (field types, subforms, entities) show here.

🎯 By the end you'll know how to

  • Prepare a form to send its data to Power BI.

  • Create a dataset and a report.

  • Understand which tables Mairu generates and how they relate.

  • Choose between Measure, Calculated table and Custom table.

  • Show the report in the form and in a dashboard.

🧠 The idea: dataset → report → where it is seen

Form summary            →   Dataset              →   Report           →   Form / Dashboard
(which fields are sent)      (which forms and         (the charts)         (where the user sees it)
                              relationships)

The tables Mairu generates

When you create a dataset, Mairu automatically generates:

Table

Contains

One per selected form

The fields marked for Power BI in its Summary

One per subform and per table of the form

Their records, so they can be analyzed separately

One per each relationship you configure (for example, Asset or Date)

The information to relate and filter

Plannings

The schedules (tasks) of those forms

States, CreationDate, CreationUser

Statuses, dates and creating users

💡 Here you see the value of lesson 6. Since the weighings are a subform with a single field peso, in Power BI you will have a Weighings table with a peso column: calculating the average, the maximum or the trend is immediate. With Weight 1… Weight 5 in a table, it would be a puzzle.

Measure, Calculated table or Custom table?

I want to…

Use

How

Calculate a value (total, average, percentage, KPI)

Measure

DAX, in real time

Generate a summary table from others

Calculated table

DAX, in import mode: not real time (it refreshes periodically)

Combine or transform several tables or sources

Custom table

SQL, with an alias for each form or system table

🛠️ Guided practice

Step 1 · Choose what is sent

  1. In the Summary of the Packaging control check the fields marked for Power BI (lesson 8): Line, Product, Shift, Result, Temperature, Rejection reason.

  2. Do the same in the Summary of the Weighings subform: mark Weight, Line and Batch.

Step 2 · Create the report and its dataset

  1. Open BI Tools in the side menu.

  2. Click New report: name Packaging quality.

  3. Enable Create new dataset: name Packaging - Dataset, and choose a date range (for example, the current month).

  4. Select the forms Packaging control and Weighings.

  5. In Relationships, add a relationship by Asset (to filter by line) and another by Date.

  6. Create the report.

Step 3 · Build the report

When you edit the report you will see the tables in the side panel. Create:

  1. A measure Average weight on the weighings table (adapt the names to those of your dataset):

Average weight = AVERAGE('Weighings'[peso])

  1. A line chart with the trend of Average weight per day and one series per line.

  2. A bar chart with the number of controls per Rejection reason.

  3. A card with the number of NOT OK controls of the period.

⚠️ If when creating the measure you don't see the peso column, go back to Step 1: the field is not marked for Power BI in the Summary of Weighings.

Step 4 · Show it where it's used

  • In the Packaging control, General tab, associate the Packaging quality report and the page you want to show. It will appear in the Power BI tab of the history.

  • In the Quality dashboard (lesson 13), add a Power BI widget with this report.

🔍 Check that it works

  • The line chart shows one series per packaging line.

  • The average weight of the report for a specific control matches the Average weight field you calculated with the query in lesson 10.

  • calidad.prueba sees the report in their dashboard and in the Power BI tab of the form.

⚠️ Common mistakes

  • Sending all fields "just in case". The dataset becomes heavy and slow. Send only what you are going to analyze.

  • "A new field doesn't appear in Power BI." You have to mark it for Power BI in the form Summary.

  • "The reports break when modifying the dataset." Changing or removing fields and relationships affects the charts that use them. Plan the changes.

  • Expecting real-time data from a Calculated table. It works in import mode and refreshes periodically; for real-time values use Measures.

  • Doing in Power BI what should be a data field. If in every report you calculate the same thing from free texts, maybe the form needed a selector or an entity.

🏋️ Challenge

Quality wants a table with one row per weighing that includes the line, the product and the shift of the control it belongs to, to study whether any shift systematically packs below weight. Build it with a Custom table: add the form and the subform with an alias each and join the tables by the parent response identifier (autocomplete will show you the available columns when you type the alias followed by a dot). Then create the average weight per shift chart.

📚 Learn more

➡️ Next lesson: Integrations - API, Webhooks and imports

Did this answer your question?