From Normalized Tables to Business Insights: Using CONCATENATEX in Moodle Analytics

[techies]

Analyzing Moodle LMS data stored in MySQL or imported into a Microsoft Fabric Lakehouse presents a consistent challenge due to its normalized schema across tables, including dfqn_course, dfqn_user, dfqn_user_enrolments, and related tables like dfqn_data_fields and dfqn_data_content.

While this structure is optimal for transactional systems and efficient write operations, it does not naturally align with the consolidated, contextual views that business stakeholders expect. Reporting scenarios often require combining multiple rows into a single, interpretable output, which is where DAX plays a critical role.

DAX includes the CONCATENATE function, which merges two text strings.

Full Name = 
CONCATENATE(dfqn_user[firstname], " " & dfqn_user[lastname])

To improve data clarity, it is essential to create a full name column by combining the first and last names from the user table. However, this function is limited to scalar values and does not address the need to aggregate multiple rows into a single representation. This limitation becomes evident when working with normalized datasets like Moodle, where relationships span across multiple tables, and one-to-many structures are common.

CONCATENATEX addresses this gap by working with tables instead of individual values. It evaluates an expression for each row in a table, concatenates the results into a single string, and allows for sorting and applying delimiters. Operating as an iterator at runtime, it processes rows one by one to create easily readable summaries from row-based data, eliminating the need for complex visuals or reports.

Consider the requirement of displaying all courses within a selected category.

In Moodle, each course exists as a separate row, but business users typically expect a single, readable list.

Courses in Category = CONCATENATEX(
    FILTER(
        dfqn_course,
        dfqn_course[category] = SELECTEDVALUE(dfqn_course[category])
    ),
    dfqn_course[fullname],
    " | ",
    dfqn_course[fullname], ASC
)

By using SELECTEDVALUE to capture the current filter context and FILTER to construct a virtual table containing only relevant rows, CONCATENATEX can iterate over the filtered dataset and return an alphabetically ordered, delimited string of course names.

This pattern is particularly effective with related tables, like user enrollments.

Since Moodle separates enrollment data from course metadata, enriching the dataset before aggregation is crucial.

Enrolled Courses = CONCATENATEX(
    FILTER(
        ADDCOLUMNS(
            dfqn_user_enrolments,
            "CourseName", RELATED(dfqn_course[fullname])
        ),
        dfqn_user_enrolments[userid] = SELECTEDVALUE(dfqn_user[id])
    ),
    [CourseName],
    ",",
    [CourseName], ASC
)

Using ADDCOLUMNS establishes a row context, allowing the RELATED function to retrieve course names for each enrollment. The FILTER function narrows the results to a specific user, and CONCATENATEX combines the course names into a single string.

The true potential of CONCATENATEX emerges clearly when you incorporate conditional logic into the expression, amplifying its effectiveness and flexibility.

For example, enrollment methods in Moodle may include free, manual, or paid options. By appending cost information only when it exists, the output becomes more informative and business-friendly.

Enrolment Methods = CONCATENATEX(
    FILTER(dfqn_enrol, dfqn_enrol[courseid] = SELECTEDVALUE(dfqn_course[id])),
    dfqn_enrol[enrol]
        & IF(dfqn_enrol[cost] <> BLANK(), " (INR" & dfqn_enrol[cost] & ")", ""),
    " · ",
    dfqn_enrol[enrol], ASC
)

Instead of simply listing enrollment types, the result communicates both the method and its financial implications, enhancing the interpretability of the data without increasing report complexity.

Beyond courses and enrollments, Moodle’s activity modules introduce another layer of complexity through dynamic field configurations.

These fields, stored in tables like dfqn_data_fields, can vary significantly across activities.

Field Types in Activity = CONCATENATEX(
    DISTINCT(
        SELECTCOLUMNS(
            dfqn_data_fields,
            "TypeLabel",
            dfqn_data_fields[type] & ": " & dfqn_data_fields[name]
        )
    ),
    [TypeLabel],
    " | ",
    [TypeLabel], ASC
)

By combining SELECTCOLUMNS with DISTINCT, it is possible to construct standardized labels that represent field types and names, and then use CONCATENATEX to aggregate them into a structured summary.

Another valuable application lies in representing relationships between students and courses.

By enriching enrollment data with user names and course identifiers, it becomes possible to generate a consolidated list of students for a given course.

Students in Course = CONCATENATEX(
    FILTER(
        ADDCOLUMNS(
            dfqn_user_enrolments,
            "StudentName",
                RELATED(dfqn_user[firstname]) & " " & RELATED(dfqn_user[lastname]),
            "cid",
                RELATED(dfqn_enrol[courseid])
        ),
        [cid] = SELECTEDVALUE(dfqn_course[id])
    ),
    [StudentName],
    ", ",
    [StudentName], ASC
)

This transformation is particularly useful in dashboards and administrative reports, where space is limited and clarity is essential. Instead of displaying multiple rows, the information is presented as a concise roster, improving both usability and readability.

At a more advanced level, CONCATENATEX can be used to construct detailed narratives from activity-level submission data. Moodle stores submissions across multiple related tables, often requiring joins to reconstruct meaningful information.

Submission Details = CONCATENATEX(
    FILTER(
        ADDCOLUMNS(
            dfqn_data_content,
            "FieldName", RELATED(dfqn_data_fields[name]),
            "RecordUserID", RELATED(dfqn_data_records[userid])
        ),
        [RecordUserID] = SELECTEDVALUE(dfqn_user[id])
    ),
    [FieldName] & ": " & [content],
    " | "
)

By combining field names with their corresponding content and filtering by user context, CONCATENATEX can produce a structured, human-readable summary of submissions.

A key concept underlying these patterns is the interaction between row context and filter context. Functions like ADDCOLUMNS introduce a row context, enabling row-by-row evaluation and the use of RELATED to fetch data from connected tables. In contrast, FILTER operates within the filter context defined by report visuals, slicers, and user interactions. Understanding how these contexts interact is essential for building efficient and accurate DAX expressions.

While CONCATENATEX is powerful, it can lead to performance issues when applied to large, unfiltered datasets due to its row iteration and in-memory operations. Ensuring proper filtering, efficient relationships, and optimized data models is critical to maintaining responsive reports.

In conclusion, CONCATENATEX is far more than a string aggregation function. It serves as a bridge between normalized LMS data structures and business-ready analytics. In the context of Moodle and Power BI, it enables the creation of compact, narrative-driven outputs that enhance clarity, reduce visual clutter, and improve decision-making. By leveraging iteration, relationships, and dynamic expressions, CONCATENATEX transforms fragmented datasets into cohesive insights, making it an indispensable tool for advanced DAX development and real-world reporting scenarios.

concatenatex.pbix