Correcting DAX Code for Ordered Maintenance Scopes

User Concerns Analysis

The user wants the displayed values to follow a specific order: High, Moderate, Light, Extended. The code attempts to sort and display these values but likely fails to achieve the desired order.

Code Inspection

1. Incorrect delimiter in ADDCOLUMNS function: The code uses semicolons instead of commas.
2. Missing SORT operation: Once the sort order is established, it should be explicitly used to sort the OrderedScopes.
3. Incorrect delimiter in SELECTCOLUMNS function: The code uses semicolons instead of commas.
4. Proper label mapping: Ensure labels are properly mapped within SELECTCOLUMNS.

Code Rectification

Original Code (for reference)

DynamicText = 
VAR SelectedScopes = 
    VALUES('OperatingProfileRankTable'[Maintenance Scope])

VAR OrderedScopes = 
    ADDCOLUMNS(
        SelectedScopes;
        "SortOrder"; 
            SWITCH (
                [Maintenance Scope];
                "High operating hours (7000h/a)"; 1;
                "Moderate operating hours (4000h/a)"; 2;
                "Light operating hours (2300h/a)"; 3;
                "Extended Maintenance Interval (4000h/a)"; 4;
                5
            )
    )

VAR SortedScopes = 
    SELECTCOLUMNS(OrderedScopes;

        "ScopeText"; 
        SWITCH (
            [Maintenance Scope];
            "High operating hours (7000h/a)"; "🟥 High operating hours (7000h/a)";
            "Moderate operating hours (4000h/a)"; "🟦 Moderate operating hours (4000h/a)";
            "Light operating hours (2300h/a)"; "🟩 Light operating hours (2300h/a)";
            "Extended Maintenance Interval (4000h/a)"; "🟫 Extended Maintenance Interval (4000h/a)";
            ""
        )
    )

RETURN
    CONCATENATEX(
        SortedScopes; 
        [ScopeText];
        ", "
    )

Corrected Code

DynamicText = 
VAR SelectedScopes = 
    VALUES('OperatingProfileRankTable'[Maintenance Scope])

/* Corrected Delimiters and Sorting */
VAR OrderedScopes = 
    ADDCOLUMNS(
        SelectedScopes,
        "SortOrder", 
            SWITCH(
                [Maintenance Scope],
                "High operating hours (7000h/a)", 1,
                "Moderate operating hours (4000h/a)", 2,
                "Light operating hours (2300h/a)", 3,
                "Extended Maintenance Interval (4000h/a)", 4,
                5
            )
    )

/* Explicitly Sort by SortOrder */
VAR SortedOrderedScopes = 
    SORTBYCOLUMNS(
        OrderedScopes,
        "SortOrder", ASC
    )

/* Corrected Delimiters and Mapped Labels */
VAR SortedScopes = 
    SELECTCOLUMNS(
        SortedOrderedScopes,
        "ScopeText",
        SWITCH(
            [Maintenance Scope],
            "High operating hours (7000h/a)", "🟥 High operating hours (7000h/a)",
            "Moderate operating hours (4000h/a)", "🟦 Moderate operating hours (4000h/a)",
            "Light operating hours (2300h/a)", "🟩 Light operating hours (2300h/a)",
            "Extended Maintenance Interval (4000h/a)", "🟫 Extended Maintenance Interval (4000h/a)",
            ""
        )
    )

RETURN
    CONCATENATEX(
        SortedScopes, 
        [ScopeText],
        ", "
    )

Explanation of Corrections

1. Delimiter Correction in ADDCOLUMNS and SELECTCOLUMNS: Replaced semicolons with commas to match DAX syntax requirements.
2. Added Sorting with SORTBYCOLUMNS: Ensures the OrderedScopes are sorted based on the SortOrder column to guarantee the intended sequence.
3. Label Mapping Verification: Ensured labels are correctly mapped within the SWITCH function ensuring intended output.

These corrections ensure the selected maintenance scopes are displayed in the desired order: High, Moderate, Light, Extended.